How to run a number of queries in sequence?

[phinix]

I do same thing each month and would like to make it easier/faster: is there a way to run many queries, one after another?
I cannot use macro, cause Run SQL is restricted to 255 characters and my queries SQL codes are longer than that.
Is here a way to run them in a row? Like run query1, wait till its finished, then run 2..3..4..

Thanks in advance for help.

 

[pr2-eugin]

DoCmd.OpenQuery would be a Start.

 

[phinix]

DoCmd.OpenQuery would be a Start.

AS far as I know openquery will run an existing query. I can do this already with macro, setting up loads of queries in a macro, but I don't want to create those queries and then run them, I would like to have all queries codes in my place.
I guess it could be done in VB, but I don't know really how
I'm reading about DoCmd.RunSQL but have no idea how to do it

 

[pr2-eugin]

In that case you need to learn a bit about Recordsets. This is where I would start : http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

 

[phinix]

Can I do this for example?

dim query1 as string
dim query2 as string

' first query

query1 = "UPDATE name... WHERE..."
docmd.runsql query1

' second query
query2 = "UPDATE name... WHERE..."
docmd.runsql query2

I'm not sure if this is gonna work in sequence or all at once?

 

[jdraw]

You may want to research db.execute .

see http://www.utteraccess.com/wiki/index.php/RunSQL_vs_Execute

Also, queries and statements run one at a time in Access.

 

[vbaInet]

Can I do this for example?
I'm not sure if this is gonna work in sequence or all at once?

Following jdraw's comment, yes you can. Query1 will run before Query2 is run. However, it's not always guaranteed that Query1 will finish executing before Query2 begins, especially on large datasets.

 

[phinix]

Following jdraw's comment, yes you can. Query1 will run before Query2 is run. However, it's not always guaranteed that Query1 will finish executing before Query2 begins, especially on large datasets.

So it works using my sample from above.
Only thing is I need to make sure that each query finishes before next kicks in. It runs on 2M records database.

How can I do it?

 

[vbaInet]

Why don't we first look at shortening the SQL string of your query. How many fields does it contain? And are you updating every field?

 

[phinix]

Why don't we first look at shortening the SQL string of your query. How many fields does it contain? And are you updating every field?

I update all fields in a table, sometimes 30 so cannot reduce the length of queries.

How can make it wait til one query finishes then start next one?

 

[vbaInet]

I update all fields in a table, sometimes 30 so cannot reduce the length of queries.

Let us see the query you say can't be shortened.

How can make it wait til one query finishes then start next one?

Run the query in a transaction (you can look into that subject) or look into one of the methods of the DBEngine.