Run MS Access Make-Table Queries in Sequence 1

[MwTV]

Context:
Would like to run two MS Access make-table queries in sequence in vba code. The make-table queries are based on pass-through queries. (Once this is successful, I will eventually try to automate the querying of the SQL Server database and exporting the query result to a MS Excel workbook using windows scheduler and an Access form.)

Additional Detail:
Ideally, I would like to have the two queries “run” in sequence when the end-user clicks on the command button that is on the form. Then, I would like the contents of the recently populated table to be exported to an Excel worksheet if the records are not exceeding 65,000 rows.

Is this feasible? Further, Is this possible? If so, how should the code below be modified to accomplish my objective? Also, do I have to close each query before proceeding to the next one?

Thanks in advance for any suggestions/assistance.


Private Sub cmdNewClientReport_Click()
On Error GoTo Err_cmdNewClientReport_Click

Dim stDocName As String

stDocName = "010_Step_01_New Clients"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "011_Step_02_New Clients"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "012_Step_03_New Clients_Final"
DoCmd.OpenQuery stDocName, acNormal, acEdit

‘If number of records in table upon the conclusion of query 012… is less than 65,000 rows,then transfer contents to MS Excel template.

DoCmd.TransferSpreadsheet 1, 8, "012_Step_03_New Clients_Final", "C:\Documents and Settings\Jciao6\Application Data\Microsoft\Templates\NewClient__Sept272007.xlt", True

Exit_cmdNewClientReport_Click:
Exit Sub

Err_cmdNewClientReport_Click:
MsgBox Err.Description
Resume Exit_cmdNewClientReport_Click

End Sub

 

[jrbarnett]

Its certainly feasible to do what you ask.

To run an SQL action query (insert/update/delete statement) without prompts, you can use:

CurrentDb.Execute SQLStatement

So this can be either a name of a query in your database or an SQL statement built in your code instead of DoCmd.OpenQuery.

You can use DCount to retrieve the number of rows in the resulting table.
To automate an export to Excel, there are several code samples on this site on moving data from Access to Excel.

John