A2K Project - sqlserver recordset not working

[vmon]

I have an A2K project running against a SQL Server backend. I am trying to use recordsets and I get "Object variable or With block variable not set" error. I think this is related to references. I have DAO 3.6 on. Code worked before going to SQL Server. Do I need ADO references to use recordsets with SQL Server? Which ones? What is the syntax if I don't need ADO references. Code I am trying to make work is:

Dim dbs As Database
Dim rs As Recordset
Dim strsql As String

Set dbs = CurrentDb

strsql = "SELECT * FROM tblCode"

CurrentDb.Execute "strsql"
OR
Set rs = dbs.OpenRecordset("strsql&quot

thanks,
vmon

 

[cmmrfrds]

Qualify these 2 objects
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Access 2K defaults to the ADODB library which contains the same objects. You can use ADO if you want, make sure you have a reference to the library set under references.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

 

[vmon]

I tried qualifying with DAO. on the database and recordset and I get the same error.

 

[cmmrfrds]

I reread and noticed you did say project - sorry. Yes, you will need to use ADO.

I set cursor location to client is 3 - 1st parm
set locktype to optimistic is 3 - 2nd parm

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

set cn = CurrentProject.Connection
strsql = "SELECT * FROM tblCode"
rs.Open strsql,cn,3,3
If rs.EOF then
msgbox "no record"
exit sub
end if
' recordset handling should be same as DAO - mostly same objects