Executing Access queries from VB code

[Yer Man]

I was wondering if anybody could tell me how to execute queries that are stored in an Access database from my VB application. Any help would be greatly appreciated.

Thanks

[KentJ]

There's lots of ways - here's any easy one that uses ADO and no code. The key here is that VB treats Access stored queries like another table.

In access save the query with a name that make sense. In VB create an ADO data control, in the properties dialog of the data control, on the RecordSource tab, select adCmdTable for the Command Type, and then select your query from the drop down box titled Table or Stored Procedure.

[Michael]

One method that I use quite often is

If the query has a parameter:

Dim strParam As String
Dim strSQL As String
Dim qdf As QueryDef
Dim rs as Recordset

strParam = "PARAMETERS [Enter Parameter:] LONG; "
strSQL = dbData.QueryDefs("qryNameOfQuery").SQL
Set qdf = dbData.CreateQueryDef("", strParam & strSQL)
qdf("Enter Parameter:") = lngParamValue
Set rs = qdf.OpenRecordset(dbOpenDynaset)

If the query does not have any parameters:

Dim strSQL As String
Dim qdf As QueryDef
Dim rs as Recordset

strSQL = dbData.QueryDefs("qryNameOfQuery").SQL
Set qdf = dbData.CreateQueryDef("", strParam & strSQL)
Set rs = qdf.OpenRecordset(dbOpenDynaset)

In both examples dbData is the opened database object

This assumes you are using DAO. I have not used this with ADO but if you can get the SQL text then you can just plug the string into the rs.Open statement.

Hope this helps.