Access 2000 ODBC Connection

[Andrew_Fryer]

Hello,

I am trying to connect to a Access 2000 database using ODBC. The connect string that I am using does not work for Access 2000, but if I convert the database to Access 97 it works correctly. This is a problem because every time I wish to change the structure of the database I have to reconvert, make the changes and then convert back again.

Does anyone have a sample connect string that I can use to connect (I use the connection object to make the database link).

Thanks in advance.

Andrew

[Spectre5000]

I will assume that you have a data source set up to access the database (no pun intended), so try something
like this:



option Explicit

Dim oConn as new ADODB.Connection

private Sub Form_Load()
oConn.ConnectionString = "dsn=?" ' The ? would be your dsn name
oConn.Open
End Sub






This is the easiest way I have seen for opening a connection to ANY database. If you don't have the dsn set up,
go into your control panel, and under ODBC data sources set up the data source. You will have to specify
the type of database and the location of said database. That way, when you use the ConnectionString statement
above, you have already defined the provider, etc., and the Open statement will open that data source. Does
this make sense?

Spectre5000

[Iouri]

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBNAME & _
";Persist Security Info=False"

I think it is 3.51 for A97 and 4.0 for A2000

Iouri Boutchkine
iouri@hotsheet.com

[Andrew_Fryer]

Thanks for that - I think I will use the previous answer as it does not involve any code rewrite as I was doing basically the same, but using the wrong Jet engine.

Is there any way of setting up the DSN connection using code, so that it persists on the system and is only created the first time the application is ran.

Thanks


Andrew

[Iouri]

CREATE AN ODBC ENTRY
You can create, edit, or delete your program's ODBC entry automatically. This code checks if an ODBC
source has been made; if not, it configures one according to your program's specification.
To edit or remove an existing ODBC source, change the fRequest parameter to the ODBC_CONFIG_DSN or
ODBC_REMOVE_DSN values, respectively:

'ODBC_REMOVE_SYS_DSN = 6&- for sys DSN

Declare Function SQLConfigDataSource Lib _
"ODBCCP32.DLL" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver _
As String, ByVal lpszAttributes As String) _
As Long
Public Sub MakeODBCDataSource()
Const ODBC_ADD_DSN = 1
' Add data source
Const ODBC_CONFIG_DSN = 2
' Configure (edit) data source
Const ODBC_REMOVE_DSN = 3
' Remove data source
Const vbAPINull As Long = 0&
' NULL Pointer
Dim lngRet
' Check if it has been done, only do this once
If GetSetting(App.ExeName, "options", _
"ODBCSetup", "No") = "No" Then
Dim sDriver As String
Dim sAttributes As String
sDriver = "Microsoft Access Driver (*.mdb)"
sAttributes = sAttributes & "DSN=MyDSN" & _
Chr$(0)
sAttributes = sAttributes & _
"DBQ=C:\Temp\Myfile.mdb" & Chr$(0)
lngRet = SQLConfigDataSource(vbAPINull, _
ODBC_ADD_DSN, sDriver, sAttributes)
SaveSetting App.ExeName, "options", _
"ODBCSetup", "Yes"
End if
End Sub


Iouri Boutchkine
iouri@hotsheet.com