How to overcome Error 91 in VBA?

[101287]

I'm getting an Error when executed the code. The error is 91 91 Object variable or With block variable not set. The error is trigger when the .Execute(MySQL) is executed.

Any suggestions? Don't know how to overcome this problem.

===========================================================
Dim acc As New Access.Application
Dim accAutoSec As MsoAutomationSecurity
Dim MySQL As String
On Error GoTo Errorhandler
'Capture current security setting
accAutoSec = acc.AutomationSecurity
'Reset the security to low temporarily to avoid the security warning
acc.AutomationSecurity = msoAutomationSecurityLow
acc.Application.Visible = False
acc.OpenCurrentDatabase strDbName, False, "mypassword"

MySQL = "INSERT INTO jsfAccountManagerDetail ( WBS, jsfRequirement, jsfAccountUmanager ) SELECT amReqXreftlb.WBS, amReqXreftlb.Number, amReqXreftlb.Account_ManagerID FROM amReqXreftlb"

With acc.CurrentDb
.Execute (MySQL) '<= The trigger to the problem.
.TableDefs.Refresh
End With

Set acc = Nothing
acc.Application.Quit

Exit_Sub:

Exit Function

Errorhandler:
If Err.Number <> 0 Then
MsgBox Err.Description, vbOKOnly, "Error"
Resume Exit_Sub
End If
End Function

 

[PHV]

CurrentDb is a volatile object.
I'd try either this:
acc.CurrentDb.Execute MySQL
acc.CurrentDb.TableDefs.Refresh

Or this:
Dim myDB As Database
Set myDB = acc.CurrentDb
With myDB
.Execute MySQL
.TableDefs.Refresh
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

The error is 91 91 Object variable or With block variable not set. "

Ummm, it is an explicit error.

You Dim acc.

You use Set acc = Nothing at the end.

But, at least in the posted code, you never Set acc in the first place.

"Object variable not set."

Are you using Option Explicit? It seems to me you should get a syntax error prior to the .Execute line.


faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

Gerry:
Dim acc As [!]New[/!] Access.Application

 

[TonyJollans]

A word of advice, whether related to your problem or not: get rid of that Dim .. As New .. construct.

This code:

Set acc = Nothing
acc.Application.Quit

.. will create an Access object (by referencing acc) and delete it (by setting it to nothing), and then create another one (by referencing acc, again after having set it to nothing) and then quit the new object. Even if you reverse the order of the lines to make it slightly more logical it will still create and delete an object to no effect.

In general, in fact in every situation I can think of, Dim .. As New .. is a bad idea.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website