Running an update query via VBA, execute command can't find the update query

[TotalRapture]

Good Afternoon!

I have a series of updates to run on a table and I was hoping to automate them with one click. I tried using the execute method as described here:

https://www.fmsinc.com/MicrosoftAccess/query/action-queries/SuppressWarningMessages.htm

The problem is, when I put my query name into the execute line and try to run I receive the error:

Run-time Error '3078':

The Microsoft Access database engine cannot find the input table or query ". Make sure that it exists and that its name is spelled correctly.

It reads to me as if it can't find the update query, but I've triple checked that everything is spelled right. Could anyone help me with some troubleshooting? Thank you all for your time! Here is the VBA:

Private Sub Command0_Click()


Dim dbs As DAO.Database
Dim lngRowsAffected As Long
'Dim lngRowsDeleted As Long


Set dbs = CurrentDb


' Execute runs both saved queries and SQL strings
dbs.Execute cstrUpdate_1, dbFailOnError


' Get the number of rows affected by the Action query.
' You can display this to the user, store it in a table, or trigger an action
' if an unexpected number (e.g. 0 rows when you expect > 0).
lngRowsAffected = dbs.RecordsAffected

End Sub

 

[Joe4]

There is an easier way to do this.

If you have already created all the Update Queries you need to run, you can put them in a Macro (using a series of OpenQuery commands).
And then you can simply click on the Macro to run them all.

If for some reason you want them in VBA code, simply set up the Macro above, as I described, then select the Macro and click on the "Convert Macros to Visual Basic" button from the Macro menu.
This will convert your Macro to valid VBA code, where you can now edit and do whatever you want to it.

 

[welshgasman]

Could it not be reporting it cannot find a table of query that is referenced in query cstrUpdate_1 ?

 

[stumac]

You are referring to cstrUpdate_1 if this the query name then it should be a string: "cstrUpdate_1"

This way is my preferred method too, if running in a loop it can be much quicker and also (like your example) you are able to get the number of affected rows. One exception is if there is multiple parameters being passed to the query, as these need to be explicitly resolved in the code.

 

[TotalRapture]

There is an easier way to do this....This will convert your Macro to valid VBA code, where you can now edit and do whatever you want to it.

Thanks Joe4! This was a quick and easy way to get it running functionally, but I am still interested in why it wasn't working the original way.

Could it not be reporting it cannot find a table of query that is referenced in query cstrUpdate_1 ?

I suppose that's a possibility but I was able to find/pull the table name from the immediate window as opposed to the query name.

You are referring to cstrUpdate_1 if this the query name then it should be a string: "cstrUpdate_1"

This way is my preferred method too, if running in a loop it can be much quicker and also (like your example) you are able to get the number of affected rows. One exception is if there is multiple parameters being passed to the query, as these need to be explicitly resolved in the code.

Thanks stumac! As far as finding the query that seemed to work. I am now getting a "run-time error '3075': unknown" prior to the update actually beginning. Googling a bit it looks like an operator error (no pun intended lol), but I can definitely do some more research. Just another hurdle I suppose!

 

[stumac]

Sounds like an issue with the underlying query. Does it run if you open it manually?

Maybe post the SQL and we can take a look.

 

[TotalRapture]

It does, and converting the macros to vba I was able to have them all run sequencially. It works well but access created the VBA to use the DoCmd function.

 

[xenou]

I think would really need to know the value of cstrUpdate_1 at runtime to debug the original code.