How to open a Query with ADO

[Marcel Nihon]

Please, how can I open a query with ADO?
The following does'nt run!

Dim con As Connection, rs As Recordset
On Error GoTo Erreur
Set con = CodeProject.Connection
Set rs = New Recordset
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
rs.Open "myQuery", con, , , acQuery
rs.MoveFirst
MsgBox rs("NumQuit")
....


Marcel Nihon Tél : 04/343.59.72
Quai Churchill, 7 - boîte 011 GSM : 0499/127.244
4020 Liège Email : (e-mail address removed)
(e-mail address removed)

 

[Van T. Dinh]

I normally use CurrentProject rather than CodeProject.

Also, you should disambigutate Connection and Recordset like:

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

 

[Brendan Reynolds]

Public Sub TestSub()

'This change required only if a DAO reference exists
'and appears before the ADODB reference.
'Dim con As Connection, rs As Recordset
Dim con As ADODB.Connection, rs As ADODB.Recordset

'On Error GoTo Erreur
Set con = CodeProject.Connection

'This change required only if a DAO
'reference exists etc., as above.
'Set rs = New Recordset
Set rs = New ADODB.Recordset

rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient

'The value of the Options argument must be one or more of the values
from the
'CommandTypeEnum or ExecuteOptionEnum enumerations. acQuery is not a
'member of either of these enumerations. It is an Access constant that
has a
'value of 1, which is the same as the value of adCmdText, so passing
this value will
'attempt to execute the literal string 'myQuery' as though it were a SQL
'statement. Of course this will fail, because 'myQuery' it is not a
valid SQL statement.
'Pass acCmdStoredProc instead to indicate that 'myQuery' is the name of
a saved query.
'rs.Open "myQuery", con, , , acQuery
rs.Open "myQuery", con, , , adCmdStoredProc

rs.MoveFirst
MsgBox rs("NumQuit")

End Sub

 

[Douglas J. Steele]

Just curious, Brendan, why you wouldn't always be explicit in your
declarations, whether or not a DAO reference exists.

Even when I'm using Access 97 with only DAO selected, I always qualify with
DAO.

 

[Brendan Reynolds]

I would, Doug. All I meant by that comment was that the change does not
*have* to be made except in those circumstances, I did not mean to imply
that it *should not* be made. References can be added or removed, or their
order changed, so I agree that it would probably be a good idea to
'disambiguate' anyway to avoid possible problems in the future. But I wanted
to draw attention to the fact that there is a more fundamental problem with
the original code that will not be fixed by 'disambiguating', namely the
passing of the wrong value in the Options argument.