Reading an .mdb file

[e.g.]

Hi, I have a sub that connects and lists the table names from an access mdb file. This is what happens: if I run the code from a regular "WindowsApplication.exe" made with VB express, everything works ok but if I run it through Autocad (as listed below) it gives me the error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine." I don't know what I am missing.

I am using Autocad 2012x64bit, Office 2007x32bit but I have the AccessDatabaseEngine_x64 installed. The code I am using is:

    <CommandMethod("test")> _
    Public Sub test()

        Call GetTables()

    End Sub

    Private Sub GetTables()

        Try

            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DP.mdb;User Id=admin;Password=;"

            Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)

            conn.Open()

            Dim schemaTable As System.Data.DataTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                                                New Object() {Nothing, Nothing, Nothing, Nothing})

            conn.Close()

            For iI As Int32 = 0 To schemaTable.Rows.Count - 1
                MsgBox(schemaTable.Rows(iI)!TABLE_NAME.ToString)
            Next



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        MsgBox("done")


    End Sub

I intend to use this coding to get some block names/paths from some tables from an access mdb file, and to insert them in the drawing, it is a library of blocks and it is maintained by someone else.

Thank you for any help.

e.g.

[DiningPhilosopher]

Try setting your standalone WindowsApplication test app to run as a 64 bit application, and see if it fails with that too.

[norman.yuan]

The ConnectionString shown in your code is clearly not for connecting to 64-bit Access DB Engine, which IS NOT Microsoft Jet DB Engine.

A bit Access DB engine history:

Prior to Access 2007, Access DB uses MS Jet DB Engine, which also comes with all version of 32-bit Windows OS (but I am not sure if it is still true for Win7. Since Access 2007, Jet DB Engine was dropped out of Access, a new database engine MS ACS Database Engine is used, which is not distributed with Windows OS automatically. With MS ACS DB Engine, one can access Access DB (mdb/mde, accdb/accde,xls), but with Jet DB Engine, one only access mdb/mde (prior to Access 2007), not accdb/accde. For the first version of MS ACS DB Engine, you MUST install Acccess in order to get the DB Engine installed. Only after strong rant, MS finally made it as free download (e.g. one can install it without installing MS Access). However, the first MS ACS DB Engine (V12.0) is 32-bit only.

With Office 2010 release, 64-bit MS Acs Db Engine became available. But if you have to use 64-bit DB Engine, then you have to use 64-bit office2010. I have heard there was work around after Office2010 SP: install 64-bit ACS DB Engine first and then Office2010 32-bit.

OK, enough history. If your standalone EXE uses the same connectionstring as you shown here, then it runs as 32-bit application and uses old MS Jet DB Engine (I do not know how you get it: you did not mention your OS). Of course the EXE will not run if later the database file upgraded from pre-Access2007 format (MDB) to post Access2007 format (accdb). And the connectionString that uses 32-bit Jet DB Engine does not work inside 64-bit AutoCAD.

In the conditioon that you did have MS ACS DB Engine 64-bit installed correctly (obviously it must be for Access2010), the connectionString should be like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[MDB/ACCDB File Path];Jet OLEDB:Database Password=password;

You would notice it is oddly enough the ConnectionString still refers to Jet OldDb for password. Do not ask me why. Ask MS 😞 That is the ConnectionString is basically the same as you use Jet DB Engine, but only change the Provider from "Microsoft.Jet.OLEDB.4.0" to "Microsoft.ACE.OLEDB.12.0".

If you also use this ConnectionString in your standalone EXE, then your EXE would run as 64-bit app.

[Hallex]

Try edited code with smal addition

        Private Sub GetTables()
            Try
                Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DP.mdb;User Id=admin;Password=;"
                'Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source==C:\DP.mdb;Jet OLEDB:Database Password=;"
                Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)
                conn.Open()
                Dim schemaTable As System.Data.DataTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                                                New Object() {Nothing, Nothing, Nothing, Nothing})

                conn.Close()
                Dim sb As New StringBuilder
                For iI As Int32 = 0 To schemaTable.Rows.Count - 1
                    sb.AppendLine(schemaTable.Rows(iI)!TABLE_NAME.ToString)
                Next
                MsgBox(sb.ToString)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            MsgBox("done")
        End Sub

 btw, both connection string is right for my settings,

but ACE connection is much faster

[e.g.]

thank you guys, yes in this way it is working. I really appreciate your help.

However, in my case I have a mixture of xp-32bit and win7-64bit computers on the network all running Office 2007 32bit, so for now I will just export the tables as text files and read them accordingly. I will wait until we migrate everything to 64bit.

Thanks,

e.g.

[Hallex]

In this case you may want to change calling function

something like this:

        Private Sub GetTables()
            Try
                Dim connectionString As String = ""
                Dim conn As New System.Data.OleDb.OleDbConnection()
                Try

                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DP.mdb;User Id=admin;Password=;"

                    conn.ConnectionString = connectionString
                    conn.Open()
                Catch

                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source==C:\DP.mdb;Jet OLEDB:Database Password=;"


                    conn.ConnectionString = connectionString
                    conn.Open()
                End Try

                Dim schemaTable As System.Data.DataTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                                                New Object() {Nothing, Nothing, Nothing, Nothing})

                conn.Close()
                Dim sb As New StringBuilder
                For iI As Int32 = 0 To schemaTable.Rows.Count - 1
                    sb.AppendLine(schemaTable.Rows(iI)!TABLE_NAME.ToString)
                Next
                MsgBox(sb.ToString)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
                MsgBox("done")
        End Sub

 Seems it would work on both releases