multiple SQL queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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?
 
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.
 
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.
 
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
 
Back
Top