Access 2000 connection string

[bill37]

I'm attempting to connect to an Access 2000 database and am not sure how to construct the connection string and process a select query. Here is what I have so far.

Dim Conn as ADODB.Connection
Dim rstImport as ADODB.Recordset

Set Conn = New ADODB.Connection
Conn.Open "Provider= Microsoft Jet 4.0 OLE DB; Data Source= P:\Systems\Ruch\sys request db\System Requests.mdb"

rstImport.Open "SELECT * FROM ImportSystemRequests2 WHERE Checked = 0 AND Done = 0", Conn, adOpenDynamic, adLockOptimistic, adCmdTable

 

[cmmrfrds]

Example of opening 2 connections and joing tables across the connections.


Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
connString2 = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee2.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString2
cn.Open connString2

sql1 = "select * from cn...employees as A inner join cn2...employees as B" & _
"ON A.employeeID = B.employeeID"

rs.Open sql1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Debug.Print "rs = "; rs(0); " "; rs(1)

Some suggestions.
1. put your sql statement in a variable it is easier to debug since you can print the variable like so. debug.print "my sql = " ; sql1
2. normally open a static cursor unless you absolutely need a dynamic cursor for some reason.
3. you need an instance of the recordset with the New operator. Dim rstImport as NEW ADODB.Recordset