queryDefs Coding problem

[slrphd]

I am working on unknown ground, here, so there is much that I do not know. I want to invoke a query from a command button on a form and Wayne Ryan suggested I learn more about queryDefs and use that approach. After a little study, it seems to be the way to go. When I searched the Query Forum, I found some example code from Jon K and modified it to fit my needs. The code is:

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim RsDischarges As DAO.Recordset

Set db = CurrentDb()
Set qDef = db.QueryDefs("qry Discharges")

qDef.Parameters("PN_PlntID") = Forms!FrmFunctions!txtPN_PlntID
qDef.Parameters("SNF_ASSM") = Forms!FrmFunctions!txtSNF_ASSMID

Set RsDischarges = qDef.OpenRecordset

When I try to run the code I get a "Compile error: User-Defined type not defined" message. So now I am confused. Isn't that what the dimension statements at the beginning do for me? Can anyone provide some insight into what I am doing wrong? Thanks.

 

[Jon K]

Depending on its version, Access may by default use DAO or ADO or both.

QueryDef is a DAO object. To use the DAO code, a reference must be made to DAO (when the code window is open, choose menu Tools, Reference... and select the Microsoft DAO 3.6 Object Library.)


The equivalent ADO code is:-

   Dim cat As New ADOX.Catalog
   Dim cmd As ADODB.Command
   Dim RsDischarges As ADODB.Recordset
   
   cat.ActiveConnection = CurrentProject.Connection
   Set cmd = cat.Procedures("qry Discharges").Command
  
   cmd.Parameters("[PN_PlntID]") = Forms!FrmFunctions!txtPN_PlntID
   cmd.Parameters("[SNF_ASSM]") = Forms!FrmFunctions!txtSNF_ASSMID

   Set RsDischarges = cmd.Execute

Note
To use the above ADO code, references must be made to:
a Microsoft ActiveX Data Objects Library e.g. 2.1
as well as the Microsoft ADO Ext. 2.x for DDL and Security
.

 

[erroldou]

QueryDefs

Hi, Hope it's ok to post on this thread rather than starting a new one....

I was wondering if anyone can help out with a question regarding QueryDef.
I've been updating the code in a database developed (not by me) in an older version of Access, so much of the code contains references to DAO. As such, I'm trying to understand how ADO differs, and have done a fair bit of reading on the subject. However, my programing knowledge is not great (yet) so it's going slowly.
The following code uses QueryDef (to update a query depending on what is selected in a list box on a form), and I haven't yet found a suitable way of re-writing the code to work with ADO. If anyone give some pointers on this code, or can recommend sites with other info on converting DAO to ADO that would be great.

Thanks

--------------------------------
Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT qry_FindMemberNumber.MemID, _ qry_FindMemberNumber.Surname, qry_FindMemberNumber.FirstNames, _ qry_FindMemberNumber.Title, qry_FindMemberNumber.MemberNumber FROM _ qry_FindMemberNumber "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblMembers.MemID) = " & _ Me.ActiveControl.ItemData(varItem) & ") Or "
Next

strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryFindSelectedMembers"
Set qry = .CreateQueryDef("qryFindSelectedMembers", strSQL)
End With

End Sub
-------------------------------

 

[Mile-O]

What's wrong with DAO?

Also, DAO.QueryDef

 

[Jon K]

In Access 2000 and later, after making references to :
Microsoft ActiveX Data Objects Library 2.1
Microsoft ADO Ext. 2.x for DDL and Security

you can use the Catalog and Command objects to create an ADO View object:-

Private Sub List2_AfterUpdate()
   Dim cat As New ADOX.Catalog
   Dim cmd As New ADODB.Command
   Dim strSQL As String
   Dim varItem As Variant
   Dim strTemp As String

   strSQL = "SELECT MemID,Surname,FirstNames,Title,MemberNumber" & _
            " FROM tblMembers "

   For Each varItem In Me.ActiveControl.ItemsSelected
      strTemp = strTemp & "((tblMembers.MemID) = " & _
               Me.ActiveControl.ItemData(varItem) & ") Or "
   Next

   strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"
  
   cat.ActiveConnection = CurrentProject.Connection
  
   On Error Resume Next
   cat.Views.Delete "qryADOFindSelectedMembers"
   On Error GoTo 0
  
   cmd.CommandText = strSQL
  
   cat.Views.Append "qryADOFindSelectedMembers", cmd
   cat.Views.Refresh
   
   Set cat = Nothing
   Set cmd = Nothing
End Sub

Notes:
(1) In some versions of Access, the query created is not visible in the Queries tab even after running the Refresh method. But you can verify that it's there by building a new query based on it e.g.

Query1:
Select * from qryADOFindSelectedMembers;

(2) In ADO, a Parameter Query is referred to as a Catalog.Procedure (as in my earlier post)
whereas a Select Query is referred to as a Catalog.View (as in this post.)
.