SQL Related Question

[azizrasul]

I know very little about SQL so pardon my ignorance here.

I know that I can create an action query SQL statement and run it using RunSQL e.g.

DoCmd.RunSQL strSQL

How do create a SQL statement for a select query? Here's what I am trying to do: -

Forms![frmData Comparison].txtMatchedSTB_BE = DCount("TestString", strSQL)

where strSQL comes from a select query. The select query SQL code from the query SQL window is

SELECT tblSTB.TestString FROM tblSFF INNER JOIN tblSTB ON tblSFF.TestString = tblSTB.TestString;

In the past I have been trying to enter the query name in the DCount function, but I would like to gently begin doing things with SQL if I can.

 

[Dugantrain]

RunSQL can only be used for Action Queries, as you have discovered. To Select data, you will need to use an ADO or DAO recordset. An example of a DAO recordset would be:

Public Function fGet_Records()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db=CurrentDB
Set rs=db.OpenRecordset("SELECT Count(*) as Record_Count FROM YourTable")

You can then do something with the record(s) which are now in this recordset object. For example, place the Record_Count field into a textbox on a form:

Me.MyTextbox=rs!Record_Count

both the DAO.Recordset and DAO.Database objects take up memory, so you need to destroy them once you've finished:

Set rs=Nothing
set db=Nothing
End Function

 

[azizrasul]

Great, it worked. Which is the more efficient way of doing a count in terms of speed i.e. your method or creating a query and using it in a DCount function.

 

[Dugantrain]

I really don't use the Domain functions that much, but from what I've read, they are the most costly way of retrieving data (stored queries are the quickest, DAO/ADO recordsets are 2nd, Domain functions are last).