Full Version: Querying the results of a DAO.recordset
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
rdemyan
May 20 2005, 01:30 AM
I use DAO all the time to retrieve and update tables. Some of my querying needs are very complex and it looks like I need to create multiple SQL statements to retrieve the final results with joins and whatever.

In an SQL statement, can I substitute a DAO recordset for a table and therefore retrieve records from the DAO recordset. So, for example.

Select Field1 From rs Where rs("Field2") = 'Some Value'

where rs is a DAO recordset. In particular, I would be retrieving records from the recordset and doing joins with tables and/or other recordsets.

Thanks.
strive4peace
May 20 2005, 12:21 PM
in your code when you construct the SQL statement...

CODE
   dim s as string, mField1 as string, mField2 as string
   mField1 = currentdb.tabledefs("tablename").fields(1).name
   mField1 = currentdb.tabledefs("tablename").fields(2).name

   s = "Select [" & mField1 & "] From [" & rs.name & "] Where [" & mField2 & "]= 'Some Value';"


and, to create a query from your SQL statement -- here is code you can put into a general module

CODE
Sub MakeQuery(pSQL As String, qName As String)

   'written by Crystal
   'strive4peace2004@yahoo.ca


   'USEAGE:
   'MakeQuery strSQL, strQname
   'OR
   'MakeQuery "SELECT Lastname, Firstname FROM Friends;", "MyQueryName"


   Dim mStr As String
    
   On Error GoTo MakeQuery_error
  
   'if query already exists, delete it
   DoCmd.Echo False
   DoCmd.SetWarnings False
   On Error Resume Next
   Err.Number = 0
   mStr = CurrentDb.QueryDefs(qName).Name
   If Err.Number = 0 Then
      DoCmd.Close acQuery, qName
      DoCmd.DeleteObject acQuery, qName
      CurrentDb.QueryDefs.Refresh
   End If
   On Error GoTo MakeQuery_error
   DoCmd.Echo True
   DoCmd.SetWarnings True
   Debug.Print pSQL
   CurrentDb.CreateQueryDef qName, pSQL
   CurrentDb.QueryDefs.Refresh
   DoEvents
  
   Exit Sub
  
MakeQuery_error:
   MsgBox Err.Description, , "ERROR " & Err.Number & "  MakeQuery"
   DoCmd.Echo True
   DoCmd.SetWarnings True
   Stop
   'Press F8 to step through code and find problem
   Resume
End Sub

**********

I use "p" before a viarable name to indicate to my code that it was a "passed" value...

qName is just the name I picked for the variable holding the query name

to use the sub:

MakeQuery "SELECT Lastname, Firstname FROM Friends;", "MyQueryName"

where
"SELECT Lastname, Firstname FROM Friends;" is the SQL statement you want to make a query with -- can be a variable name too -- ie: MakeQuery strSQL, "MyQueryName"

"MyQueryName"
is whatever you want your query to be called
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.