VB.NET to connec to MDB file using windows default ODBC JET driver-CodePudding

I am building a Windows Winforms Desktop Application using VB.NET 6.0.5, Visual Basic, and Visual Studio Community 2022.

I used to connect to ACCDB File using Microsoft Access Database Engine.

It's working well in the development machine, but when I try to deploy to a fresh installation of a Windows 10 x64 machine; I have to let the setup download and install the ACE engine.

I want to reduce the prerequisites as much as possible, as the end-users already have to download and install the .NET 6 Runtime.

I found out that all versions of Windows come by default with Microsoft Access Driver (*.mdb) and I want to use that.

The driver location is: C:\Windows\SysWOW64\odbcad32.exe

I think if someone installed Office it might update the driver to a different Jet.OLEDB version.

So my question is:

How to check the current Jet.OLEDB driver's version and what would be the connection string?

My current connection string is:

Dim cStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;User ID=Admin;Password=123;"

But still, it's giving that Microsoft.Jet.OLEDB.4.0 Provider is not registered.

I know that ACE Engine and ACCDB work better but it's enough for me to use JET and MDB.

Please help!

Thank you

Update: 20/5/2022

Things that I tried but with no success:

I compiled the application to target x86 as it's said in many references that Jet.OLEDB doesn't support x64.
I tried Provider=Microsoft.Jet.OLEDB.10.0.

CodePudding user response：

Well, if you wondering what the connection string should be? Let VS build it for you.

(you don't want to hand code, or type in connection strings anyway - and you don't have to).

So, in setting for your VS project, then here:

So, let click on that [...] and build the connection using JET (as opposed to ACE).

However, your issue/problem is that NOW vs2022 is x64 bits (first version to be that way).

I'm running 2019, so it will STILL test and VERIFY a x32 bit connection. I suspect that vs2022 WILL NOT pass the "test" button, but you STILL can use it to build the conneciton.

So, in above, we start the connection wizard, - hit change for the defaulted sql server, and we now have this choice:

Ok, we selected Access, but what about JET vs ACE.

Well, click on advanced - this:

And now we can (get to) choose the provider.

this:

So, you can choose jet or ACE.

choosing jet, then we now back to here:

Now BECAUSE I am running vs2019, then my test connection WILL work!!! - for you, it probably will not. So, the test connection button for you - you can try it, but EVEN if it gives a fail message, your connection is still ok.

This:

Ok, at this point, we are done.

We now in code can use the above connection in our code. Say like this:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Using conn As New OleDbConnection(My.Settings.TestJET)
        Using cmdSQL As New OleDbCommand("SELECT * FROM tblHotels", conn)

            conn.Open()
            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)

            Dim strMSG As String =
                "There are " & rstData.Rows.Count & " Hotels in the database " & vbCrLf &
                "The first Hotel is " & rstData.Rows(0).Item("HotelName")

            MsgBox(strMSG, MsgBoxStyle.Information, "Table info")

        End Using
    End Using

End Sub

And we get this:

Now, I suppose I could look at the connection string - but I never really do that - I always let the system build that for me.

I get this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\Rides_be.mdb

As you noted, MAKE sure your project is set to x32. However, since you running vs2022, then the connection builder should work, but you HAVE to run your project to test that connection, I don't think in vs2022, you can use "test" connection.

On the other hand, since most of office (and even Access ACE is x64 bits?). Then if you build a connection using x64 bit ACE, then your test connection button should work.