ODBC /OLEDB Connection

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.

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



"Mark" <Mark@discussions.microsoft.com> wrote in message
news:C28DCFA1-4715-4D89-ABDF-57AA2F2ECFE8@microsoft.com...
> 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.
>

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










"Sahil Malik" wrote:

> 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
>
>
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:C28DCFA1-4715-4D89-ABDF-57AA2F2ECFE8@microsoft.com...
> > 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.
> >
>
>
>

On Sun, 26 Sep 2004 21:27:01 -0700, "Mark" <Mark@discussions.microsoft.com> wrote:

¤ 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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)