CODEWITHSUNDEEP
excelms-accessvba
PocketLoan
PocketLoan

Reputation: 532

Intermittent error with Running Access Queries through Excel VBA

I have a .xlsm file which compiles the entries from 5 Access database tables in a new spreadsheet then opens Access, and runs a couple queries to update the master table (I know, the architecture of the DBs is awful...) The problem arises (sometimes) with the following code, 

Dim accessApp
Set accessApp = CreateObject("Access.Application")
accessApp.UserControl = True
accessApp.OpenCurrentDatabase ("G:\Dir1\Dir2\Processing Database.accdb")
DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteFTP", acViewNormal, acEdit

On the OpenQuery line I get a 'Runtime error 2486 you cannot carry out this action at the present time', is there some kind of code library I need to load or something to prevent this intermittent problem?

Upvotes: 1

Views: 326

Answers (2)

HansUp
HansUp

Reputation: 97111

I think your Processing Database.accdb database includes a saved query named DeleteFTP and you want to execute that query.

If that is true, qualify DoCmd with the application object accessApp ...

accessApp.DoCmd.OpenQuery "DeleteFTP", acViewNormal, acEdit

Note that if your Excel project does not include an Access reference, it won't recognize those Access constants, acViewNormal and acEdit. In that case, you can use the constants' values instead of their names:

accessApp.DoCmd.OpenQuery "DeleteFTP", 0, 1

However I'm uncertain whether those suggestions are adequate because you said the problem is intermittent and it seems to me that your current code would always fail. So there is something else involved which I don't understand.

Turning off SetWarnings suppresses information. I don't know whether it's hiding clues in this case, but suggest you avoid it anyway. You won't need it if you use the DAO.Database.Execute method with its dbFailOnError option to execute your delete query ... 

'DoCmd.SetWarnings False
'DoCmd.OpenQuery "DeleteFTP", acViewNormal, acEdit
accessApp.CurrentDb.Execute "DeleteFTP", 128 ' dbFailOnError = 128

Upvotes: 2

MatthewD
MatthewD

Reputation: 6761

Try early binding the access application.

Dim accessApp As Access.Application
Set accessApp = New Access.Application
accessApp.UserControl = True
accessApp.OpenCurrentDatabase("G:\Dir1\Dir2\Processing Database.accdb")
accessApp.DoCmd.SetWarnings False
accessApp.DoCmd.OpenQuery "DeleteFTP", acViewNormal, acEdit

You might also want to try opening the database in exclusive mode.

accessApp.OpenCurrentDatabase("G:\Dir1\Dir2\Processing Database.accdb", true)

Upvotes: 1

Related Questions