Excel Driver for ADODB Connection

[Anonymous]

Hi All,

i have been using this function in Inventor 2010. we now upgraded to 2016 and this function does not work anymore.

I also have got Active x and Excel library references loaded.

Seems "dbconnection.Open dbconnectionstring is not working now. Is this related to inventor migrating to EXCEL API?

i am lost  and any help is appreciated.

Jiten

Public Function ReadDataFromWorkbook(SourceFile, SourceRange As String) As Variant 'Public Function Since it is also used in Frm_iproperties
' requires a reference to the Microsoft ActiveX Data Objects library
' requires a reference to the Microsoft Excel 14.0 Object library
' if SourceRange is a range reference, this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference, this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnectionString As String, dbConnection As ADODB.Connection, rs As ADODB.Recordset
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=False;DBQ=" & SourceFile
    'dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & SourceFile & ";Extended Properties=Excel 8.0;HDR=Yes"
    Set dbConnection = New ADODB.Connection
    MsgBox dbConnectionString
    On Error GoTo InvalidInput
    'dbConnection.ConnectionString = dbConnectionString
    dbConnection.Open dbConnectionString ' open the database connection ' 32bit office clashes with 64 bit inventor here
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:

    MsgBox "The source file or source range is invalid!" & SourceFile & err, vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function

[norman.yuan]

When it worked for your Inventor 2010, it must be this combination: Inventor 2010 is 32bit and the OS is WinXP. Here you have 2 issues:

1. Database engine: Microsoft.Jet.Oledb.4.0. Prior to MS Access 2007(or Access2010?)/Windows Vista, this DB engine comes with Windows OS. You are now likely using newer Windows (7, 8, or 10), which does not have this DB engine, unless you have older MS Access installed (MS Access 2007 or older). Thus, you cannot use Microsoft.Jet.Oledb.4.0 any more. However, you can download Microsoft Access DB Engine, which is the replacement of Jet Engine (but not coming with Windows OS automatically). If you (or your app's user have later MS Access installed, this DB Engine then is available.

2. However, since now you are using 64-bit Invetor, the Microsoft Access DB Engine must be 64 bit (make sure you downloaded 64 bit one, not 32-bit one). If your (or your user's) computer somehow already has 32-bit MS Access (or MS Office that includes MS Access), then you cannot install 64-bit DB engine. You either use 32-bit Inventor, or you use 64-bit MS Access (or remove MS Access). If you use older version of MS Office (2010), it is tricky to install 64-bit DB engine with 32-bit Office in the same computer.

If you could port/upgrade your app to use AutoCAD .NET API, you would have much more and easier options.

By the way, in your cose (of simply reading data from Excel sheet via ADODB, there is no need to set reference to Excel object library: the code does not actually open/start Excel application in order to read data in *.xls file.

[Anonymous]

Thank you norman.

Sorry, I should have deleted Microsoft.Jet.Oledb.4.0. I had commented that out so its not executed.

The ADODB connection is not working. I think Microsoft Excel Driver is  not working, may be it is 32bit Driver. 

I am basically a design engineer with half baked knowledge in VBA. Also i dont understand 32 bit and 64 bit thing quite well but i know it does create issues. from you reply i think i am stuck in this one.

My Actual Problem

There is one big excel file and i need some data from it(Not all the data). Opening this Excel file takes up a long time so this database method used to work out faster.

Can you suggest some examples where such thing has been done and compatible with 2016 Inventor. if you can share any link that you may know of will be of immense help.

Thanks and Regards,

Jiten 

[norman.yuan · Accepted solution]

Yes, the issue is caused by the fact that the VBA in your newer Inventor (must be 2014 or newer, right?) is 64-bit VBA, while the driver for accessing data in the Excel sheet is 32 bit.

Assume you are using 32-bit MS Office 2013 or later in your (or your users') computer, so that you do not have problem to install 64-bit MS Access DB Engine (download from here).

So, download and install the DB engine, in AutoCAD VBA, set reference to MS ActiveX Data Object Library 2.8, then you can try following code:

Option Explicit

Public Sub ConnectToExcel()

    '' Excel file
    Dim xlsFile As String
    xlsFile = "C:\Temp\MyData.xlsx"
    
    '' 2 columns and 8 rows data, with first row as Header
    Dim dataRange As String
    dataRange = "A1:B8"
    
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    
    '' Use MS Access DB Engine 64 bit,
    '' Notice that "HDR=YES;" treats the first row as header
    Dim cnString As String
    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & xlsFile & _
        ";Extended Properties=""Excel 12.0;HDR=YES;"""
        
    Set cnn = New ADODB.Connection
    cnn.Open cnString
    MsgBox "Connection!"
    
    On Error Resume Next
    Set rs = cnn.Execute("[" & dataRange & "]")
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description
    End If

    Do While Not rs.EOF
        MsgBox rs.Fields(0).Value & vbCrLf & rs.Fields(1).Value
        rs.MoveNext
    Loop
    rs.Close
    
    cnn.Close
    MsgBox "Done."

End Sub

Again, only one version of MS Access DB Engine can be installed (32, or 64 bit), meaning, if the computer has 32-bit MS Access installed, you cannot install 64-bit DB Engine.

But the better solution is probably giving up VBA and move to .NET API, because there is much less supported tools, components can be used in 64-bit VBA.

[Anonymous]

Hi Norman,

Talked to IT Guys here and confirmed the issue. You Nailed it. I need to update my office to 64bit. i was not able to install the Driver for 64 bit as 32 bit office is installed and needs to be removed and 64 bit office to be installed. that would take its own time but i think whatever you have shown is bound to work after that.

Thanks and Regards,

Jiten 

[norman.yuan]

Just want to make sure that you only need to remove 32-bit MS Office is because your MS Office suite includes MS Access. If you do not have MS Access installed, then you can install 64-bit MS Access DB Engine without along with 32-bit MS Office, provided the MS Office is Office 2010 or later (for Office 2010, it is tricky: you need to remove it first, and then install the 64-bit DB Engine, then reinstall the 32-bit office).

Of course you may have to do this to all possible users of this application, which could be an issue. IMO, it is not worth extra effort on porting old 32-bit VBA to 64-bit VBA apps in many, or even most cases, because there are so many needed components hardly available and the replacements (such as this in your case) either difficult to use or not suitable.