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

TotalRapture

New Member
Joined
Jun 30, 2017
Messages
15
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
Could it not be reporting it cannot find a table of query that is referenced in query cstrUpdate_1 ?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I think would really need to know the value of cstrUpdate_1 at runtime to debug the original code.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top