Iterate Databases and Delete Query

Question

I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of Set badqueryname = "qry_InformationMailer" and what I am trying to do with that is set the name of the query to delete. This is the error that line throws

object required

Full syntax:

Option Compare Database
Option Base 1
Private Sub fur()
  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
  Dim badqueryname As String
  'Compile error of object required on below line
  Set badqueryname = "qry_InformationMailer"
  Set rstTableName = CurrentDb.OpenRecordset("tbl_Data")
  If Not rstTableName.EOF Then
    rstTableName.MoveFirst
    intArraySize = rstTableName.RecordCount
    iCounter = 1
    ReDim myArray(intArraySize)
    Do Until rstTableName.EOF
      myArray(iCounter) = rstTableName.Fields("ProgramName")
      iCounter = iCounter + 1
      rstTableName.MoveNext
    Loop
End If
If IsObject(rstTableName) Then Set rstTableName = Nothing
  Set qd = CurrentDb.QueryDefs("qry_InformationMailer")
  Set ws = DBEngine(0)
  For l = LBound(myArray) To UBound(myArray)
    Set db = ws.OpenDatabase("C:\" & Trim(myArray(l)) & ".mdb")
    For Each qryLoop In CurrentDb.QueryDefs
        If qry.LoopName = 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

HansUp · Accepted Answer · 2015-06-23 15:13:59Z

Don't use Set when you assign a value to a simple variable type.

'Set badqueryname = "qry_InformationMailer"
badqueryname = "qry_InformationMailer"

Or, if you wish, you could use Let instead of Set.

Let badqueryname = "qry_InformationMailer"

But I don't see Let used very often anymore.

Actually, in your code the value of badqueryname never changes again after you assign its value. In other words, it's being used like a constant, so you could just make it a constant.

  Const badqueryname As String = "qry_InformationMailer"

One of those changes should eliminate that object required error. However, then you will encounter an error with the undeclared qry in this line ...

If qry.LoopName = badqueryname Then

Add Option Explicit to your module's Declarations section and then run Debug->Compile from the VB Editor's main menu.

The compiler should also complain here because l is undeclared ...

For l = LBound(myArray) To UBound(myArray)

Fix anything the compiler complains about and then run Debug->Compile again. Repeat until no more compile errors.

Collectives™ on Stack Overflow

Iterate Databases and Delete Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related