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)


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top