ODBC & OLEDB

[Guest]

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

 

[Rami Saad]

Hello,

Here are some samples using ODBC. They are working fine.

Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

Hope this helps you.
If you still face problems, can you send your connection string, as well as
the error that you get? This would help us identify your problem.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[Guest]

Hi Rami,
Thanks for your reply.
My original post contains the connection strings and the error messages that
I am getting. You might need to scroll down to see.
I am getting the connection string from the DataLink API and am not hard
coding it.

Regards,
Mark