ODBC /OLEDB Connection

[Guest]

Dear All,

I am "trying (struggling) to" create an user interface with VB.NET that
will allow me to connect to existing Acess databases through ODBC as well as
OLEDB. Due to many reasons I do not wish to hard code the
connection strings.
I am using the Data Link API to obtain the connection strings at run time.
The codes works fine with OLEDB to connect and run queries but with ODBC it
generates a system error.
I simply CANNOT figure out the following:

1. What is wrong with the ODBC connection string?
2. Why the same logic/code works with OLEDB and not with ODBC as the
connection string is obtained in similar way?

I am at the end of my patience and might end up doing something desperate.
If any kind hearted person is listening --please help.
Please find the codes below:

Thanks & Regards,
Mark

'Getting the Connection String
Dim udl As String = Application.StartupPath & "\Data.udl"
IO.File.Delete(udl)
IO.File.Create(udl).Close()
Process.Start(udl).WaitForExit()
Dim sw As New IO.StreamReader(udl)
sw.ReadLine()
sw.ReadLine()
Dim conn As String = sw.ReadToEnd
sw.Close()
'Display the Connection String in a Text Box.


txtConnection.Text = Conn
Conn = (Conn.Trim)
dt = New DataTable

If InStr(Conn, "MSDASQL", CompareMethod.Text) = 0 Then
Dim nwindConn As OleDb.OleDbConnection = New
OleDb.OleDbConnection(Conn)
nwindConn.Open()
schemaTable =
nwindConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing,
Nothing, Nothing, "TABLE"})
nwindConn.Close()
Else
MsgBox("ODBC Connection")
Dim co1 As New Odbc.OdbcConnection(Conn.Trim)

'The code works fine till this point. It creates a system error in the next
line.

co1.Open() ' System Error is created here.


Dim cm As Odbc.OdbcCommand = New Odbc.OdbcCommand("", co1)
schemaTable = cm.ExecuteReader.GetSchemaTable
co1.Close()
End If


' The Exception occurs at Co1.Open() and the message is:

'An unhandled exception of type 'System.Data.Odbc.OdbcException'
'occurred in system.data.dll
'Additional information: System error.

 

[Sahil Malik]

Can you paste an example of a connection string that works in Oledb, but
does not work in ODBC? Without looking at the connection string, I am
guessing you might have the "Provider" specified in the connection string.
(Just a guess).

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Guest]

Hi Sahil,
The strings are as follows:
1.) File Data Source Connection String: This One does not work

DBQ=C:\DataTest.mdb;DefaultDir=C:\Documents and Settings\Administrator\My
Documents\25 Test 1\Black\WindowsApplication1\bin;Driver={Microsoft Access
Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common
Files\ODBC\Data
Sources\ppp.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;



2.) Selected from DataSourceList: This one does not work:
Provider=MSDASQL.1;Persist Security Info=False;Data Source=SOF8991S3A


3.) 'OLEDB STRING: (THIS ONE WORKS !!!!)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataTest.mdb;Persist
Security Info=False

 

[Paul Clement]

¤ Hi Sahil,
¤ The strings are as follows:
¤ 1.) File Data Source Connection String: This One does not work
¤
¤ DBQ=C:\DataTest.mdb;DefaultDir=C:\Documents and Settings\Administrator\My
¤ Documents\25 Test 1\Black\WindowsApplication1\bin;Driver={Microsoft Access
¤ Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common
¤ Files\ODBC\Data
¤ Sources\ppp.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;
¤
¤
¤
¤ 2.) Selected from DataSourceList: This one does not work:
¤ Provider=MSDASQL.1;Persist Security Info=False;Data Source=SOF8991S3A
¤
¤
¤ 3.) 'OLEDB STRING: (THIS ONE WORKS !!!!)
¤ Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataTest.mdb;Persist
¤ Security Info=False

For reliability and feature support I highly recommend that you use Jet OLE DB instead of ODBC and
the MS Access driver.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)