ODBC & OLEDB

From: Mark (Mark_at_discussions.microsoft.com)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 01:45:02 -0700

Hello,

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?

Thanks & Regards,
Mark

Please find my codes below:

'Getting the 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 connection strings are:

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

Relevant Pages

  • ODBC /OLEDB Connection
    ... The codes works fine with OLEDB to connect and run queries but with ODBC it ... What is wrong with the ODBC connection string? ... It creates a system error in the next ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ODBC /OLEDB Connection
    ... does not work in ODBC? ... Without looking at the connection string, ... > OLEDB. ... It creates a system error in the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Remove DB connections
    ... What drivers are you using? ... Proprietary, OLEDB or ODBC? ... Are you use the same connection string each time? ...
    (microsoft.public.inetserver.asp.db)
  • Re: Connection to ODBC without a DSN
    ... I just need a way now to get the connection string used so I can save it for ... > As far as ODBC vs. OLEDB is concerned, OLEDB is nice, it is a lot more ... > flexible than ODBC, but it's also a lot more difficult to use. ... >> is required for the data access api you are using with your ...
    (microsoft.public.sqlserver.odbc)
  • Re: ODBC /OLEDB Connection
    ... File Data Source Connection String: ... Security Info=False ... > does not work in ODBC? ...
    (microsoft.public.dotnet.framework.adonet)