Connection String Problem

[ReportsNerd]

Hi, I am getting an unhandled exception on my connection string line below when running. Not sure what the issue is, but I get the following message when running from VS 2010 Pro:

Format of the initialization string does not conform to specification starting at index 33.

Code:
Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\TestDB1.accdb"
con.ConnectionString = dbProvider & dbSource
con.Open()
da = New OleDb.OleDbDataAdapter(Sql, con)
da.Fill(ds, "Debtors")
len = da.Fill(ds, "Debtors")
End Sub

Also, is there a way I can shorten the dbSoruce line since I have included the MS Access database in my project?

Help appreciated.
Thanks.

 

[IanRyder]

Hi,

Your connection string is slightly incorrect. Is should be declared as follows:-

Dim oledbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Access Databases\northwind.mdb'")

Notice the words "Data Source" and then the single quotes to define the actual database location and name.

Hope that helps.

Cheers,

Ian

 

[ReportsNerd]

Hi Ian, I changed it to the following, but now I get "unrecognized database format" on my con.Open line. Not sure why that is unless it has something to do with the newer access database extension (.accdb)? Thanks.

Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
'dbSource = " C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\TestDB1.accdb "
'con.ConnectionString = dbProvider & dbSource
Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\TestDB1.accdb'")
con.Open()
da = New OleDb.OleDbDataAdapter(Sql, con)
da.Fill(ds, "Debtors")
len = da.Fill(ds, "Debtors")
End Sub

 

[ReportsNerd]

Correction, I used: Provider=Microsoft.ACE.OLEDB.12.0
and that worked great, thanks!