Macro RUNSQL vs Queries

[Geoff]

For discussion's sake....

I've just finished developing 9 or 10 queries, (makes, appends, deletes),
that will take 14 imported ISAM files, normalize some of the nastier data,
and extract "yesterday's" business into a tab delimited file.

Now I want to automate all of it into a nightly process, so I start building
a macro and realize that I can't actually use the queries in the macro, I
have to copy the sql statements and use RUNSQL.

Here's the discussion part...

Why go through the trouble of building individual queries if you're just
going to build a macro anyway?

Why can't macro building include sql building?

Should I delete the queries, now that their sql is in the macro?

If I don't delete the queries why should I bother updating two sets of sql,
(query & macro)?

Should I always build queries and keep them updated even if I'm going to put
it all in a macro?


Remember, it's a discussion, so feel free to contribute.

Thanks!
Geoff
An Old Fogey stuck
in COBOL, trying break out.

 

[Ken Sheridan]

You don't have to repeat the SQL statements, can call the OpenQuery action in
a macro. With an 'action' query this executes the query. You'll probably
need to call the SetWarnings action, setting it to No before executing the
queries, and back to Yes afterwards if you want then all to execute without
user confirmation.

While macros are cheap and cheerful a better option would be to use code to
execute the queries within a transaction. That way, in the event of an
error, you can either handle the error and resume or roll back the
transaction, so you are not left in the lurch with half updated data. The
following is a simple little procedure I once wrote to demonstrate the basics
of how transactions work, using a couple of queries in which errors could be
deliberately raised by messing with the sample data.

Public Sub TransactionDemo()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb

wrk.BeginTrans
strQuery = "qryU1"
dbs.Execute strQuery, dbFailOnError
strQuery = "qryU2"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans

Exit_Here:
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub

Ken Sheridan
Stafford, England