ODBC Driver Login Failed

[Guest]

While using VBA script to refresh Excel Pivot Tables from Access I am getting
error message "ODBC Microsoft Access Driver Login Failed- The Database has
been placed
in a state by user 'Admin' on machine 'RX1' that prevents it from being
opened or
locked". By pressing alt-tab after the line

oWB.PivotCaches.Item(1).Refresh

I can see that error message is an Excel an error message. I am then
prompted with a Login Window for a "Data Source - MS Access Database" it is
asking me for "Authorization - Login name and Password." I already have Admin
rights on this machine. Nothing I enter works so code gives me "Error 1004 -
Application Defined or object defined Error".

Help please!



Private Sub cmdTest4_Click()
Dim oRs As ADODB.Recordset
Dim oCnn As ADODB.Connection
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook

On Error GoTo ErrorHandler

'Connect to your Access db
Set oCnn = Application.CurrentProject.Connection

'Create your recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT * FROM My_Query;", oCnn, adOpenDynamic,
adLockOptimistic, adCmdText
'Create an instance of Excel
Set oApp = New Excel.Application
oApp.Visible = False
'Set oWB = oApp.Workbooks
Set oWB = Workbooks.Open(Filename:="C:\My Documents\refPiv1.xls")
oWB.Worksheets("testSheet4").Activate

'ref ptable
oWB.PivotCaches.Item(1).Refresh

'Clean up ADO Objects
oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
'Clean up Excel Objects
oWB.Close SaveChanges:=True
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing
End Sub

 

[Guest]

A colleague of mine believes that the error is because somewhere earlier in
my code, I have opened the excel file already. It is like I need to get the
permission of the person who opened the excel file before I can manipulate it.

Can anyone please see where I am going wrong?

 

[Guest]

Further coding has revealed that this error message does not occur if I do
the code in another database. If I want my code to run more than once, I have
to restart the database containing the code. Excel doesn't close when my sub
routine ends.

Private Sub cmdRefPiv7_Click()

Dim oRs As ADODB.Recordset
Dim oCnn As ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim strSQL As String
Dim AccessConnect As String

On Error GoTo ErrorHandler

'Connect to your Access db
Set oCnn = New ADODB.Connection

AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=test4.mdb;" & _
"DefaultDir=C:\Documents and Settings\ma\My Documents\Dcom_2006_3\Acc
pract;" & _
"Uid=;Pwd=;"

oCnn.ConnectionString = AccessConnect 'Set oCnn =
Application.CurrentProject.Connection
oCnn.Open
Cmd1.ActiveConnection = oCnn
Cmd1.CommandText = "SELECT * FROM myQuery;"
Set oRs = Cmd1.Execute

Set oApp = New Excel.Application
Set oWB = Workbooks.Open(Filename:="C:\Documents\Piv3.xls")
oWB.PivotCaches.Item(1).Refresh

oRs.Close
Set oRs = Nothing
Set Cmd1 = Nothing
oCnn.Close
Set oCnn = Nothing
'Clean up Excel Objects
oWB.Close SaveChanges:=True
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing

End Sub

Any further suggestions please?