Help reqested on an error in accessing a query from VBA

B

bbs

Can someone help me with the below problem:
when I try to read from a ms-access table in VBA all works fine;
when I use exactly the same on a query (with the same content) it goes into
an error:

run-time error 3021
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

VBA source =
Dim InSet As ADODB.Recordset
Set InSet = New ADODB.Recordset
InSet.Open "qryTest", CurrentProject.Connection
InSet.MoveFirst
 
W

What-a-Tool

This is the way that has always worked for me:

Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim strSQL As String
strSQL = "My SQL Statement"

rst.Open strSQL, con, adOpenKeyset, adLockReadOnly

Or - as I was told in another post that DAO is the way to go using a Jet
database (By Douglas J. Steele in Post "Transfering data from recordset to
recordset question"), here is the DAO method:

Dim dbCurr As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as String
strSQL = "My SQL Statement"
Set dbCurr = CurrentDb()
Set rst = dbCurr.OpenRecordset(strSQL)

Hope I gott the DAO bit right - I haven't actually ever used this method -
you can check the post it came from - it's the 5th RE: in from the top


--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous
 
Top