multiple SQL queries

Y

yoyoh2ogirl

How can I run a series of SQL queries in Access? I have over 100 queries that
I need to run and I wanted to know if I can avoid opening a separate query
window for each.

Thanks for your help.
 
K

Ken Snell

Run them? Just to see them? Are they select queries? totals queries? action
queries? What will you do with the queries when you run them?
 
K

Ken Snell

So you want to do the queries automatically instead of having to run each
one manually?

I assume that you have saved all the queries in the database. If so, then
you can use a macro or VBA code to run the queries.

In a macro, you'd use OpenQuery action.

In VBA, you likely would use the Execute method of the Database object.

Post back on which process you'd like to do.
 
Y

yoyoh2ogirl

Yes, I want to run the queries automatically. I've written a long SQL script
that calls up all the queries. They're not saved as individual queries, so I
don't want to use a macro where I'd have to use OpenQuery, which means I'd
have to save each query individually and then put each one into a macro.
That's what I was trying to avoid. I wanted to be able to just run the script
as if I was using SQL Server. Is there a way to do that in VB? Do I have to
load the queries as a string?

What I'm doing now is opening a query window in SQL mode, copying the query
text from Word, pasting it into the query window, running the query, then
going on the next query in Word. I was hoping I could just load them all into
one query window and running the whole thing, but I discovered that a query
window can only handle one query at a time.
 
K

Ken Snell

The SQL statements already exist, but you don't want to store the queries in
the database?

This is generic code for running queries in VBA code:

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb()
' Repeat the next two lines for each query that you want to run
' ... change the actual SQL statement for each
strSQL = "SELECT TableName.Field FROM TableName (etc.)"
dbs.Execute strSQL, dbFailOnError

dbs.Close
Set dbs = Nothing
 
Top