I am building an Excel spreadsheet to acquire some data (table format) from a server using a customized Excel Add-in. After that I need to modify that table and I thought the best way to do it is using a ADODB connection to the same Excel spreadsheet and SQL queries.
When I try to establish the ADODB connection using Microsoft ACE OLEDB 12.0 driver having used before a connection with that Excel Add-in, I always get the same error:
Run-Time error '-2147467259 (80004005)
Unspecified error
This is the code I am using to establish ADODB connection:
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim sconnect As String
Set Conn = New ADODB.Connection
DBPath = ThisWorkbook.FullName
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& DBPath & ";" & "Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
Conn.Open sconnect
Conn.Close
If I swap the connection order, connect first to my Excel using ADODB and then connect to the external server using the Add-in, I do not get this error.
The problem is, sometimes I have already established the Add-in connection using a different spreadsheet and when I open this spreadsheet with the ADODB connection, the error is triggered, because Excel retains that plug-in connection if I do not restart the Excel session.
I have tried looping through Excel connections but it does not pick up the plug-in connection:
For i = 1 To ActiveWorkbook.Connections.Count
Next
Is there any way to know/debug why I get this error.
