Unable to get ADO to fill Access Recordset

[Calvin Willman]

I don't get any errors with the following code, but the debug shows that the
recordset although created isn't being filled. I don't know if the
connection is opening or not. This used to work on a different system, which
I'm now applying to a new company I'm working for and I just haven't got a
clue why this won't work. Any ideas??

Dim strRs As String, rs As ADODB.Recordset

Set rs = CreateObject("ADODB.Recordset")

strRs = "SELECT * FROM tblKPIs;"

Dim cn As ADODB.Connection
Dim strConnection As String

Set cn = CreateObject("ADODB.Connection")
strConnection =
"Driver=C:\windows\system32\odbcjt32.dll;DBQ=C:\Documents and
Settings\Calvin.NTDOMAIN\Desktop\KPIDB.mdb;"
cn.ConnectionString = "DSN=KPI;" ' I've created an ODBC
Data Source User type entry, pointing to the Database file on the desktop,
called KPI.
'cn.ConnectionString = strConnection
cn.Mode = adModeRead 'you may need a different mode
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open

rs.Open strRs, cn, adOpenDynamic, adLockPessimistic

' Then I manipulate the Recordset here....

 

[Calvin Willman]

Do away with connection settings and just have

Set cn = CurrentProject.Connection