Finding the correct OleDb connection string VB.Net

Question

I am creating a 32bit app on an old XP machine, this app will run on Win7 or later as well.

so I am trying to test for what connection string will work. Such as:

Private Function test_ace_or_jet(ByVal mdb_path As String) As String
        Dim connString As String
        Dim dbMaintPort As OleDb.OleDbConnection

        connString = ""

        Try
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mdb_path

            dbMaintPort = New OleDb.OleDbConnection(connString)
            dbMaintPort.Open()

            dbMaintPort.Close()

            MsgBox("1 was found")
        Catch ex As Exception
            MsgBox("Could not open 1" & vbCrLf & vbCrLf & ex.Message)
            Try
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdb_path
                dbMaintPort = New OleDb.OleDbConnection(connString)
                dbMaintPort.Open()
                dbMaintPort.Close()

                MsgBox("2 was found")
            Catch ex2 As Exception
                connString = ""
                MsgBox("Could not open 2" & vbCrLf & vbCrLf & ex2.Message)
            End Try
        End Try
        test_ace_or_jet = connString
    End Function

However the issue that I've found is that on the XP machine, the first connection string does not fail. It does not have ACE installed at all.

How can I test whether to use one or the other? Am I able to get the above function to give me the correct connection string?

DRapp · Accepted Answer · 2013-02-05 04:09:03Z

1

if you keep doing your try/catch until you find something, you might be nesting yourself quite deep... You may be better (while testing), doing a loop and have an array of connection strings something like

Private Function test_ace_or_jet(ByVal mdb_path As String) As String
   Dim connString As String
   Dim dbMaintPort As OleDb.OleDbConnection
   Dim ValidConnection As Boolean
   Dim finalConnection As String
   Dim connStrings(2) As String
   connStrings(0) = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mdb_path
   connStrings(1) = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdb_path
   connStrings(2) = "AnyOtherProviderTestConnectionStrings..."

   ValidConnection = False
   For value As Integer = 0 To 2
      Try
         dbMaintPort = New OleDb.OleDbConnection(connStrings(value))
         dbMaintPort.Open()
         dbMaintPort.Close()

         MsgBox("Success: " & connStrings(value))
         ValidConnection = True
         finalConnection = connStrings(value)
      Catch 
      End Try

      if ValidConnection
         exit
      endif 

   Next

   ; continue with whatever else you want to do with test result and final string

End Function

Also, have you had a look at ConnectionStrings.com? They might give you more options, and connection samples to test different providers and such.

answered Feb 5, 2013 at 4:09

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hank Over a year ago

Hi @DRapp, this is more elegant I will use it! However this does not solve the issue that it does not fail on connecting to the ACE engine. My XP machine that I'm testing on, does not have ACE yet it accepts it as a valid connection string, it even opens the database. I need to somehow test if ACE is available or not.

DRapp Over a year ago

Hank, you state you do not have ACE in stalled... However, that probably comes via other software, like Office/Access... have you checked something like C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

Hank Over a year ago

Hi @DRapp, just checked and you are correct I have both... nuts!

Collectives™ on Stack Overflow

Finding the correct OleDb connection string VB.Net

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related