|
You can use any SQL code (as long as Access Jet understands it):
dim rst as Recordset
dim db as Database
dim strSQL as string
dim qdf as QueryDef
set db = CurrentDB()
strSQL = "SELECT *...."
set rst = db.OpenRecordSet(strSQL, dbOpenDynaset) ' or dbOpenSnapshot
if rst.RecordCount > 0 then
do until rst.EOF
rst.MoveFirst
... handle each record
rst.MoveNext
loop
If you're intested in action queries that update tables but not necessarily
returns records, you could use:
strSQL = "DELETE
set qdf = db.CreateQueryDef("") ' this is a temporary query
qdf.SQL = strSQL
qdf.ReturnsRecords = False ' or true if records are returned
qdf.Execute
If the query does return records, you can access these one by one too:
rst = qdf.OpenRecordSet (dbOpenTable) ' or dbOpenDynaset or dbOpenSnapshot
Theo
|
|
...and if you were worried about the SQL statement itself, using the
Orders sample database of MS Access does what you want with the SQL
statement:
SELECT DISTINCTROW Orders.CustomerID, Count(Orders.OrderID) AS CountOfOrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID;
Theo
|