Unanswered: Writing Pass Through Queries in VBA #2
I am working with a function written by moderator Sinndho, and posted in an older thread HERE
The problem I am having is, I am getting an error from the function.
Error 3265 Item not found in collection from this line of code
"If Not IsNull(dbs.QueryDefs(QueryName).Name)"
I am calling this function from the code example on post #7 of the previous thread, entering in the SQL string and connection information pertinent to my situation. The SQL string was tested in the server management studio.
I am not sure how to properly paste code into this message , so I hesitate to post the whole function in plain text.
Anyway, if someone would be kind enough to look at the previous post and help me understand why I am getting this error.
The purpose of this line is to determine whether a Querydef object with the same name as the one provided by the this argument (Optional ByVal QueryName As String) already exists in the CurrentDb database object. An alternate method would consist in querying the MSysObjects table, like this:
If DCount("*", "MSysObjects", "Name='" & QueryName & "' AND Type=5") > 0 Then DoCmd.DeleteObject acQuery, QueryName
A query with the name "QueryName" (if you pass any, of course) should be present in the database after calling the function. You can use this query like any other query in the database (open it, open a recordset from it, assign the RecordSource of a form to it, assign the RowSource of a ListBox or ComboBox to it, create a new query from it, etc...). Notice that sometimes, you need to use the F5 key in the database window to refresh the display.