Opening a query with code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I access a query in my code? I've tried this:
Dim record as adodb.recordset
set record=new adodb.recordset
record.open contactquery, currentProject.Connection, adOpenKeyset,
adLockPessimistic, adcmdquery

It lets me access the query, or should I say it let's me access the table
the query is based on, but it doesn't show the effects of the query. This is
so frustrating. My query has filtered and sorted the table, but none of that
shows up in my code when I access the recordsets.

How do I do what I'm trying to do if I going about this wrong?
Many thanks,
charlie (CK)
 
CK, this is not real code. I know this because adcmdquery is not a valid
option for the ADO Recordset Open method, so the code you have posted would
not compile. Please don't ask people to debug air-code, it wastes everyone's
time - including your own!

The following test works for me. The table contains two fields, while the
query selects only one, and sorts in descending order. The result in the
Immediate window confirms that the recordset contains only one field and is
sorted in descending order.

Public Sub OpenADORecordsetOnQuery()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "qryTest", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdStoredProc
Debug.Print rst.Fields.Count = 1
Debug.Print rst.Fields(0).Value = 3
rst.MoveLast
Debug.Print rst.Fields(0).Value = 1

End Sub
 
Back
Top