CODEWITHSUNDEEP
sqlexcelvbaactivexadodb
Tom
Tom

Reputation: 31

Excel crashes on ADODB connection open command

I'm trying to use vba in Excel (2013) to use ADODB connection and SQL to query and collate data from another tab. My laptop is running Windows 10 Pro 64-bit(10.0.17763) and Excel Standard 2013 64-bit (15.0.5233.1000)

I have tried removing and re-adding my Active X References, but has not solved the issue. I've also tried removing all possible references and re-adding them one by one as well as trying to use older versions. All result in the same result. My references chosen are: Visual Basic for Applications, Microsoft Excel 15.0 Object Library, OLE Automation, Microsoft Office 15.0 Object Library, Microsoft Forms 2.0 Object Library, Microsoft ActiveX Data Objects 6.1 Library, Microsoft ActiveX Data Objects Recordset 6.0 Library.

When I get to the cnExcel.Open in the below code, excel just crashes(i.e. closes) with no error messages.

Dim cnExcel As Connection
Dim rsExcel As Recordset
   .....
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Users\trheinsc\Desktop\InProgress\Error Dashboard_TEMPLATE.xlsm"";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cnExcel = CreateObject("ADODB.Connection")
Set rsExcel = CreateObject("ADODB.Recordset")
rsExcel.CursorLocation = adUseClient
cnExcel.Open strCon                     ' <=  This is where Excel blows up and shuts down.

' Populate Iteration Columns
sqlSelect = "SELECT DISTINCT [Iteration]"
sqlFrom = "FROM [ErrorDetail$]"
sqlWhere = "WHERE [Iteration] Is Not NULL"
sqlGroup = ""
sqlOrder = "ORDER BY [Iteration] DESC"
sqlString = sqlSelect & " " & sqlFrom & " " & sqlWhere & " " & sqlGroup & " " & sqlOrder
rsExcel.Open sqlString, cnExcel
cDashRow = 1
cDashCol = FirstIterCol
Do
    If rsExcel.EOF Then Exit Do
    shDashboard.Cells(cDashRow, cDashCol) = "Iter " & rsExcel(0) & Chr(10) & "# Errors"
    shDashboard.Cells(cDashRow, cDashCol).Columns.AutoFit
    aIterArray(cDashCol) = rsExcel(0)
    cDashCol = cDashCol + 1
    rsExcel.MoveNext
Loop Until rsExcel.EOF
LastIterCol = cDashCol - 1
If rsExcel.State = 1 Then rsExcel.Close

Any assistance would be greatly appreciated.

Upvotes: 2

Views: 2247

Answers (3)

Tom
Tom

Reputation: 31

After much extensive searching for a solution to get past this issue, i found that recommendations on other providers to do the connection. The original (1st) and 2nd using JET didn't work, but the 3rd using MSDASQL did work.

So i left all 3 types of calls in my code and just commented out the ones that didn't work. 

strFile = ThisWorkbook.FullName
'Using Microsoft.ACE.OLEDB Provider - If you get an issue with ACE OLEDB Provider try JET Provider
'strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

'Using Microsoft.Jet.OLEDB Provider - If you get an issue with Jet OLEDB Provider try MSDASQL Provider (above statement)
'strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

'Using MSDASQL Provider
strCon = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strFile & ";HDR=Yes';"

Upvotes: 1

Dy.Lee
Dy.Lee

Reputation: 7567

I use it like this.

Dim Rs As Object
Dim strConn As String
Dim i As Integer

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 12.0;"


Set Rs = CreateObject("ADODB.Recordset")
Rs.Open strSQL, strConn

Upvotes: 1

timlash
timlash

Reputation: 89

Clearly a connection string problem (isn't it always?). When connecting to an XLSM file in Excel 2013 the Extended Properties needs to include "Excel 12.0 Marco", not "Excel 12.0" as you have it.

Upvotes: 2

Related Questions