[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Run queries sequentially

Posted on 2004-11-03
12
Medium Priority
?
886 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:ajakeway
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12488836
yeah

Do it like this

Sub RunMyQueryies()

currentdb.execute "MyFIrstQueryNameHEre"
currentdb.execute "MySecondQueryNameHEre"
currentdb.execute "MyThridQueryNameHEre"
'ect....



End sub
0
 
LVL 8

Expert Comment

by:SimonLarsen
ID: 12488890
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

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

'and keep going....

        Exit Function
       
ErrHandler:
       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.)
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12489840
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.

Mike
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 34

Expert Comment

by:flavo
ID: 12489848
Nice point Mike
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12489928
:-)

Mike
0
 
LVL 2

Author Comment

by:ajakeway
ID: 12494357
flavo,

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.

Alex
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12494455
Dave is in down under and sleeping now...

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

Mike
0
 
LVL 2

Author Comment

by:ajakeway
ID: 12494483
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?

Alex
0
 
LVL 8

Expert Comment

by:SimonLarsen
ID: 12497222
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.
0
 
LVL 2

Author Comment

by:ajakeway
ID: 12497519
Simon,

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.

Thanks,

Alex
0
 
LVL 8

Accepted Solution

by:
SimonLarsen earned 2000 total points
ID: 12497702
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'
go
select * into _Staging_2003_ajakeway from jk.cdate WHERE y_ear = '2003'
go

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.
0
 
LVL 2

Author Comment

by:ajakeway
ID: 12649574
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.

Alex
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question