Issuing Queries to '97 Database

[Jeremy]

I need to update many tables and columns in an Access '97 database. I prefer
to write my SQL statements manually (instead of using the graphical query
builder or wizards). I understand how to use Access '97 to manually create a
query ("SQL view") and then click the little ! button to get it to run. But
what I'd like to do is be able to create many UPDATE statements in one
editor window and execute any one of those at a time. This would be akin to
using SQL Server Management Studio or Query Analyzer to issue sql batches or
*portions* thereof to a SQL Server db. Is there any way to do the equivalent
with an MS Access '97 database - or some way to get Query Analyzer or SSMS
to connect to an Access '97 database? Any 3rd party tool? Anything? Or am I
limited to issuing one query at a time by clicking the ! button.

Thanks!

 

[david epsom dot com dot au]

dim db as dao.database
dim rs as dao.recordset
dim qdf as dao.querydef

set db = codedb
set rs = db.openrecordset("tblSQL")
set qdf = db.openquerydef("qdfSQL")

while not rs.eof
qdf.sql = rs!sql
qdf.execute
rs.movenext
wend

(david)

 

[Brendan Reynolds]

There is nothing built-in, but it would not be particularly difficult to
create something to do that in Access. You'd store your SQL statements in a
table, use a form to view and select them, and code behind your command
button would open a recordset on the selected records and execute the SQL
statement stored in each selected record.