Help With Syntax

[jo15765]

This is my code, and it does not present any errors however, it doesn't delete the query or import the query. I put this into my masterdatabase that has the fully functioning and working query that I need to export to all databases listed in the table. Can someone point out what my issue is, and how to remedy?


Option Compare Database
Option Base 1
Private Sub formulaOne()
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rstTableName As DAO.Recordset
Dim myArray() As String
Dim intArraySize As Integer
Dim iCounter As Integer
Dim qryLoop As QueryDef
Dim exists As String
Dim dbs As Database
Const badqueryname As String = "qry_mailmerge"
Set rstTableName = CurrentDb.OpenRecordset("Information")
If Not rstTableName.EOF Then
rstTableName.MoveFirst
intArraySize = rstTableName.RecordCount
iCounter = 1
ReDim myArray(intArraySize)
Do Until rstTableName.EOF
myArray(iCounter) = rstTableName.Fields("DatabaseName")
iCounter = iCounter + 1
rstTableName.MoveNext
Loop
End If
If IsObject(rstTableName) Then Set rstTableName = Nothing
Set qd = CurrentDb.QueryDefs("qry_mailmerge")
Set ws = DBEngine(0)
For l = LBound(myArray) To UBound(myArray)
Set db = ws.OpenDatabase("L:\\" & myArray(l) & ".mdb")
For Each qryLoop In CurrentDb.QueryDefs
If qryLoop.Name = badqueryname Then
exists = "Yes"
DoCmd.DeleteObject acQuery, badqueryname
Exit For
End If
Next
On Error Resume Next
db.CreateQueryDef qd.Name, qd.SQl
db.Close
Set db = Nothing
Next l
End Sub

[jonh]

Docmd works on the active database - currentdb, not the one you've opened with vba.

[jo15765]

Docmd works on the active database - currentdb, not the one you've opened with vba.

How can I iterate the querydefs of the database opened through my VBA?

[jonh]

Through the db object you created.
dbs.querydefs(myqd)

[jo15765]

I must be looking at the incorrect line. I altered my syntax to this, and it is still only deleting the query from the database that I am running this syntax from

For Each qryLoop In dbs.QueryDefs(myqd)

[jonh]

Qyerydefs is a collection.

E.g

Set x = db.querydefs("somequery")

Or

For each itm in db.querydefs
...
next

[jo15765]

So my syntax should read:

For each itm in db.QueryDefs
If qry.itm = badqueryname Then
exists = "Yes"
DoCmd.DeleteObject acQuery, badqueryname
Exit For
End If
Next

[jonh]

Docmd runs on the current database.

What is qry.itm?


dbs.querydefs.delete itm.name

If you want to know if something exists or not, just set a variable and catch the error.


On error resume next
Set badqry = dbs.querydefs(badqryname)
If err.number <> 0 then 'query exists
'update it's sql
Badqry.sql = mysqlstring
Else 'doesn't exist
Msgbox badqryname & " doesn't exist"
End if