Queries in VBA

[jex57]

Hi all,

In desperate need of assistance.

I am currently building a statistical view of data - % data missing, % complete etc.

I am the writing these values into a stats table so that one is able to see progress.

The only way I can do this is via a number of queries, and to keep things neat I am doing this via VBA

I cant get to update the stats table, can append to it.

The steps that I am doing are as follows:

1 - Append data to temp table sorted by domain - data looks like this:

Domain ID Number of fields Number of empty fields
Domain 1 182
Domain 2 184
Domain 3 90
Blank 100


This works

Next I want to update the number of empty fields (I cant append as it creates a new record, which I dont want.

This is where I get stuck.

My code currently looks like this:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL1 As String
Dim Sql As String
Dim NumberOfFields As Integer
Dim DomainCat As Integer
Dim NumberofEmptyFields As Integer
Dim NumberofEmptyBT As Integer
Dim rst As DAO.Recordset
On Error Resume Next

On Error GoTo 0
' Count number of Fields in total
newSQL1 = "INSERT INTO TblTempStats ( FieldCount, DomainCatID )" _
& "SELECT Count(TblField.FieldID) AS CountOfFieldID, TblField.DomainCatID" _
& " FROM TblField GROUP BY TblField.DomainCatID"



DoCmd.RunSQL (newSQL1)



'count number of empty fields


Set dbs = CurrentDb


newSQL1 = "SELECT TblField.DomainCatID, Sum(IIf([fieldname] Is Null,1,0)) AS EmptyField" _
& " FROM TblField LEFT JOIN TblTempStats ON TblField.DomainCatID = TblTempStats.DomainCatID" _
& " GROUP BY TblField.DomainCatID"




Set rst = dbs.OpenRecordset(newSQL1, dbOpenDynaset)


With rst
Do Until rst.EOF
Sql = "UPDATE rst LEFT JOIN TblTempStats ON rst.DomainCatID = TblTempStats.DomainCatID SET TblTempStats.EmptyFields = 'EmptyField'"
dbs.Execute (Sql)
End If
.MoveNext
Loop
End With
rst.Close
dbs.Close



Any help would be appreciated ( the bold lines are where I am struggling, I dont know how to use the results of the first query to update my table)

 

[ranman256]

1. If you are in Access, there's no need to use vb for queries. Just make queries. You are making more work.
2. If you are in Access, there's no need to use DAO, (or ADO) , Access is already connected to the data. You are slowing things down.

 

[xenou]

Update with left join is not always feasible. Doesn't always make sense either (if there is no join field that matches then at best you are updating to null, which isn't an extremely clear result).

It is in fact easier to not script queries although you can do so.

If you just want to make it easier you can still write the queries without vba, and run them with vba:

Sub RunQueries()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.SetWarnings True
End Sub

If nothing else this is easier to test and write, so you probably at least want to start out with regular queries at first, until they all work.

It is very hard to debug your particular queries as your table data and table relationships are not very clear to outsiders - need sample data or more information about what is in the tables. For instance, it is completely unclear why you would think to put a query in a loop and run it repeatedly, or why you want to update the number of empty fields when your original data shows the number of empty fields already in the table.