How to

A

aspfun

I got an error in ADP file but it is working in MDB file.

The error is pointed at following line. How to fix it?

Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)
-------------------------------------------
Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim strQueryName As String
Dim strMySQL As String

strSQL = "select QueryName from tQueryListA"

Set dbs = CurrentDb()
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)
 
D

Dirk Goldgar

aspfun said:
I got an error in ADP file but it is working in MDB file.

The error is pointed at following line. How to fix it?

Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)
-------------------------------------------
Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim strQueryName As String
Dim strMySQL As String

strSQL = "select QueryName from tQueryListA"

Set dbs = CurrentDb()
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Click to expand...


In an ADP, there is no CurrentDb (the function returns Nothing), and you
would normally use ADODB recordsets and objects, not DAO. Your equivalent
to the above code might look like this:

'----- start of suggested code -----
Dim cn As ADODB.Connection
Dim rec As ADODB.Recordset
Dim strSQL As String

strSQL = "select QueryName from tQueryListA"

Set cn = CurrentProject.Connection
Set rec = New ADODB.Recordset
rec.Open strSQL, cn, adOpenKeyset
'----- end of suggested code -----

Be aware that ADODB recordsets aren't exactly the same as DAO recordsets, so
it's possible that something you intend to do with this recordset later in
the code might need adjusting as well.
 
Top