Creating MS Access ODBC link to SQL Database 1

[benaround]

Anyone have Microsoft Access 2000 Visual Basic code where I can create an ODBC link to a SQL 2000 database in a MS Access dataabase?

I know its been done, like 2mill times, but now I actually have a need for the code and am having problems.

Thanks ahead of time for any help, its greatly appreciated.

 

[POSOL]

Hey benaround,

Here is your code for accessing SQL Server through ODBC link: SQL_ODBC is the name of ODBC connection that you have to set up. + u have to create a referece to DAO objec:
Tools->References - and check mark on Microsoft DAO , i used 3.6.

- free world, free use

Private wrkODBC As Workspace
Private cnnSQL As Connection
Private rstSQL As Recordset

Private SQL_ODBC As String 
Private SQL_SERVER As String
Private SQL_DATABASE As String
Private SQL_TABLE As String
Private SQL_USERID As String
Private SQL_PASSWORD As String

'setup workspace
Set wrkODBC = CreateWorkspac("ODBCWorkspace", "admin", "", dbUseODBC)
wrkODBC.DefaultCursorDriver = dbUseClientBatchCursor

'setup connection
Set cnnSQL = wrkODBC.OpenConnection(SQL_ODBC, dbDriverNoPromt, False, "ODBC;UID=" & SQL_USERID & ";PWD=" & SQL_PASSWORD & ";Server=" & SQL_SERVER & ";Database=" & SQL_DATABASE & ";DNS=" & SQL_TABLE)

'get recordset
Set rstSQL = cnnSQL.OpenRecordset("SELECT * FROM " & SQL_TABLE & " WHERE (ID = '" & txtComments & "')", dbOpenDynaset, 0, dbOptimisticBatch)