Get Access querydefs with ADO.

Shouldn't it be

Set RS = CMD.Open, ......

?

vindevogel :

Thank you to answer.

I am not sure I understood your comment.

The adodb.command object does not have 'open' method.

Laurent_Diep.

Try using this:-
set rs = cmd.execute

Can you post your code for the Access Query.  I have a feeling that it has something to do with that.

Wileecoy.

Try this



With Conn
    .CursorLocation = adUseClient
    .ConnectionString = strConn
    .Open
End With

With Cmd
    Set .ActiveConnection = Conn
    .CommandText = "Productsbyid"
    .CommandType = adCmdStoredProc
    .Parameters("ProductID") = 3
    RS.CursorLocation = adUseClient
    RS.CursorType = adOpenStatic
    RS.LockType = adLockReadOnly
    Set RS = .Execute
End With

Below is documentation from VB help.

I beleive your problem does lie in the code that is in your query.  If you were previously using DAO the SQL query starts with "Parameter".  When using ADO, it must start with "Select" when you are collecting records based on parameters.

The following documentation actually goes through RDO and ADO, but I decided to include it all.


Also - compare the information in this link to the other link you were given.  This one is for DAo and begins with the "Parameter " keyword.  This may be the problem you are having.

http://support.microsoft.com/support/kb/articles/Q142/9/38.asp?LN=EN-US&SD=gn&FR=0&qry=Microsoft%20Access%20QueryDef%20Parameter%20Using%20VB&rnk=6&src=DHCS_MSPSS_gn_SRCH&SPR=VBB


Hope This Helps

Wileecoy.



--------------- begin example --------------

Executing a Parameter Query


RDO
The procedure below illustrates a method for performing a parameterized Select query: that is, a SELECT statement that requires one or more parameters. This process is done in two steps:

Build a query that expects a parameter, pass in the first parameter, then perform the query.


Perform the query again with new parameters.
The first time the query is called, RDO attempts to create a new RDO query object. Since the object is appended to the rdoConnection object?s rdoQueries collection, you can reference it each time the procedure is called. Each subsequent time the procedure is called, the Refresh method re-executes the query. This technique builds a temporary stored procedure (SP) behind the scenes that is referenced by the Requery method. The temporary SP is dropped when the connection is closed.

Private Sub ParmQueryButton_Click()
   Dim Qy As New rdoQuery
   Dim rs As rdoResultset
   Static FirstTime As Boolean
   If cn.rdoQueries.Count = 0 Then
      FirstTime = True
      sql = "select * from authors where year_born = ?"
      Set Qy = cn.CreateQuery("Pq", sql)
   End If
   Qy(0) = QueryParam.Text
   If FirstTime Then
      Set rs = Qy.OpenResultset()
      FirstTime = False
   Else
      rs.Requery
   End If
   rdoGrid1.ShowData rs
   rs.Close
End Sub

ADO
This procedure is designed to perform a table-access query that accepts a parameter. You use the "?" character (as in the previous RDO example) to indicate where the parameter is to be placed. In this case, though, you don't create an rdoQuery object that is kept in a collection off the rdoConnection object; you instead use a stand-alone ADO Command object created (and scoped) earlier. The first time through, you set up the Command properties, and each time thereafter, you simply execute the command after having changed the parameter.

ADO gives you a lot of flexibility here?more, in some cases than RDO. If you tell ADO everything it needs to know about a query, it won't have to perform informational queries against the database to get missing information, so queries run faster.

Note   You don?t have to build the ADO Parameters collection in code, since it's automatically created for you just like when you use RDO. However, it is possible to do so, and doing so can improve performance, at the cost of a little code complexity. If you elect to do it, make sure that the Command is associated with an open connection so ADO can query the service provider (and the server) for the parameter's description.

To run the query and create the resultset, use the Execute method on the Command object.

Private Sub ParmQueryButton_Click()
   If Cmd.CommandText = "" Then
      Cmd.ActiveConnection = cn
      With Cmd
         .CommandText = "select * from authors where year_born = ?"
         .CommandType = adCmdText
         .CommandTimeout = 15
      End With
'
'   The following section of code is not required,
'    but can make execution faster. It eliminates the need
'   for ADO to fetch the parameter metrics from the server.
'
      With Parm
         .Type = adInteger
         .Size = 4
         .Direction = adParamInput
         .Value = QueryParam.Text
         Cmd.Parameters.Append Parm
      End With
   End If

   Cmd.Parameters(0).Value = QueryParam.Text
   Set rs = Cmd.Execute()
   ADOGrid1.ShowData rs
   rs.Close
End Sub


--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.

Thank you to answer.

wileecoy :

Here is the SQL code for the access querydef
PARAMETERS ProductID IEEEDouble;
SELECT Produits.[Ref produit], Produits.[Nom du produit], Produits.[N? fournisseur], Produits.[Code categorie], Produits.[Quantite par unite], Produits.[Prix unitaire], Produits.[Unites en stock], Produits.[Unites commandees], Produits.[Niveau de reapprovisionnement], Produits.Indisponible
FROM Produits
WHERE (((Produits.[Ref produit])=[ProductID]));

Your second sample with :

Cmd.CommandText = "select * from authors where year_born = ?"

does not suit me because I want to use and already created Access querydef to increase performance.

rpai :

I try to manage with Access Query.

hes :
I copy and paste your code and there is the same error message.
Does this code work for you ?

Thank you all to answer.


Thank you.        

The code works with the OLE DB driver for DSN (with an already existing DSN Entry) but doesn't work with the driver for Jet 3.51.

Could someone make it work with a dsnless connection ?

Thank you.