Run queries sequentially

Hello.  Due to the size of my pass-through queries, I need to run them sequentially rather than at the same time, and I'd like to set them up to run overnight.  Is there a way (ie macro / VBA script) that would allow me to do this?

If I set up the macro to run the queries, does it wait until the first is done before it does the second?

Thanks,  Alex
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Do it like this

Sub RunMyQueryies()

currentdb.execute "MyFIrstQueryNameHEre"
currentdb.execute "MySecondQueryNameHEre"
currentdb.execute "MyThridQueryNameHEre"

End sub
Do the pass through return a success / failure code?

If so use ADO to execute them

Function SeqRun

        On Error GoTo ErrHandler
        dim retcode as Integer
        Dim cmd1 As New ADODB.Command
        cmd1.CommandType = adCmdStoredProc
        cmd1.CommandText = "Passthrough1"
        cmd1.Parameters.Append cmd1.CreateParameter("ret", adInteger, adParamReturnValue)
        cmd1.ActiveConnection = ocon

        Dim cmd2 As New ADODB.Command
        cmd2.CommandType = adCmdStoredProc
        cmd2.CommandText = "Passthrough1"
        cmd2.Parameters.Append cmd1.CreateParameter("ret", adInteger, adParamReturnValue)
        cmd2.ActiveConnection = ocon

        retcode = cmd2.Parameters("ret")
If retcode <> 0 then goto ErrHandler

'and keep going....

        Exit Function
       msgbox "something died"      
        End Function

For which you'll need a handy dandy ocon function:

Function ocon() As ADODB.Connection

svr = "ServerName"
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Provider=sqloledb;" & _
           "Data Source=" & svr & ";" & _
           "Initial Catalog=DataBaseName;" & _
           "Integrated Security=SSPI"

Set ocon = con
End Function

If they don't just put an extra line at the end of the proc you run that says return 0

(you may want to do some error checking but that is sort of further into it.)
IF you use this as Dave says, add the dbfailonerror

currentdb.execute "MyFIrstQueryNameHEre", dbfailonerror

Access will automatically wrap it in a transaction and roll back if it fails.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nice point Mike

ajakewayAuthor Commented:

Sorry it's taken me a bit.  This was urgent last night until the network dropped at work and IT said it was too late for them to be working.

I tried this:

Sub Try2()
CurrentDb.Execute "aappacctJOINcatacct", dbFailOnError
CurrentDb.Execute "aaOLAPrank", dbFailOnError
End Sub

And I'm getting a "Run-time error '3065': Can't execute a select query"

This query runs fine by itself.  It's all written in SQL rather than the Access GUI.  I've tried it with the queries closed and open but the same error.  Must be something small, but I don't know Access enough.

Dave is in down under and sleeping now...

The execute method only works with action queries. (Update, Insert, Make-Table and Delete)

ajakewayAuthor Commented:
Simon Larsen,

I tried your code and it gave an error compiling:
    Function ocon() As ADODB.Connection
saying it is a user-defined type not defined.

Is the fact that I'm using Access 97 an issue?

Ahhh right, yes. By default ADO is not enabled in Access 97. If it is even available.

Go to a module. Click on Tools / references and Hunt through for a version of Microsoft Active Data Objects.

Enable that and you should be away.

What are these pass throughs though? They aren't select statements are they? That would make some difference.
ajakewayAuthor Commented:

They are pass-through queries, and select statements.  For instance:

SELECT * FROM jk.cdate WHERE y_ear = '2004'

The query is more involved than that, and for all periods in the year it caused the database to seize up so IT asked me not to run it for all periods.  For a given period it takes about 90 minutes.  So I was hoping to run the first query, then the next and so on overnight so I could sleep and have it work for me.

I thought it might even be as simple as using the Access macro system and just selecting Run this query, Run that query.  But I don't use Access enough to know what commands to choose.


Ummm select statements?

What were you planning on doing when you get in? Copy it to excel or something?

As I said my solution will only really work with stored procedures.

Try rewriting it in sql, rather than Access and whacking the data into staging tables or DTS'ing them out or something.

You could do this a lot more elegantly from sql than from Access.

So make your pass through

select * into _Staging_2004_ajakeway from jk.cdate WHERE y_ear = '2004'
select * into _Staging_2003_ajakeway from jk.cdate WHERE y_ear = '2003'

This will run sequentially on the sql box. You could also use WAITFOR DELAY 00:05 to put a little delay in there in case you are causing cascading locks, tran log blocking etc.

When finished

Select * from _Staging_2003_ajakeway

Will be quick and easy result set.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajakewayAuthor Commented:
Thanks for all the help.  I was able to ask somebody here (when they got back from holidays) and just set up a macro, rather than using vba.  But I do appreciate the answers.  Thank you.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.