audioboxer
New Member
- Joined
- Aug 13, 2012
- Messages
- 23
Hi Everyone,
I needed a solution for retrieving data from Access and using my trusty MrExcel “VBA and Macros” book I came up with the below code. This code works beautifully until I close Excel, reopen the File and try to run it again, at which point I get this error.
“Run-time Error ‘-2147417848 (80010108)’:
Automation Error
The Object invoked has disconnected from its clients.”
If I try to debug or end, excel just crashes. This code will not work again successfully until I repair Microsoft Office via Add/Remove programs. Any ideas? It has something to do with the connection to Access because when I try to Import Data via the built in Ribbon icon, this also crashes excel until I repair Microsoft Office.
Using Microsoft Office Standard 2010 x86
I needed a solution for retrieving data from Access and using my trusty MrExcel “VBA and Macros” book I came up with the below code. This code works beautifully until I close Excel, reopen the File and try to run it again, at which point I get this error.
“Run-time Error ‘-2147417848 (80010108)’:
Automation Error
The Object invoked has disconnected from its clients.”
If I try to debug or end, excel just crashes. This code will not work again successfully until I repair Microsoft Office via Add/Remove programs. Any ideas? It has something to do with the connection to Access because when I try to Import Data via the built in Ribbon icon, this also crashes excel until I repair Microsoft Office.
Code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
sSQL = "SELECT Vendor FROM Vendors"
MyConn = "C:\Users\sbg\Desktop\TestData.accdb"
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, CursorType:=adOpenDynamics, LockType:=adLockOptimistic, _
Options:=adCmdText
rst.MoveFirst
With Me.VendorList
Do
.AddItem rst![Vendor]
rst.MoveNext
Loop Until rst.EOF
End With
rst.Close
cnn.CloseUsing Microsoft Office Standard 2010 x86
