VBA/Module Query Automation (1 Viewer)

[admessing]

Hey there....

I would like to automate a series of queries that update various output tables for my DB. But, I am not exactly what you would term a VBA programmer.

I could use a bit of help in writing the code to get things running. Could someone (or several someones) give me a chunk of basic code that I can alter to suit my needs?

Some of my queries actually make tables, others append tables, thus some pre-existing outputs need to be deleted before they are rebuilt. I would prefer if the idiot messages didn't popup during the process.

Thanks!

Be sure to comment in the code strings so that I know what to do with it and can understand what is going on.

 

[tehNellie]

An easy solution I use is to create a table to hold the queries that I want to run as part of a process with a RunOrder Column

ie

RunOrder        QueryName
1              qryINSERT_AnExcelFile
2              qryUPDATE_thatData
3              qryUPDATE_adifferentBitofData

I then have a query that selects that data, sorted by Run Order

SELECT QueryName
FROM tblImport_Data
ORDER BY RunOrder

in VBA I then open that query into a recordset, step through it row at a time and execute each query found in queryName in turn

dim db as dao.database
dim rs as dao.recordset
dim qdf as dao.querydef

set db = currentdb
set rs = db.openrecordset("qrySELECT_importdata")

  do while not rs.eof
    set qdf = db.querydefs(rs![queryname])
    
    qdf.execute dbfailonerror

    rs.movenext
  loop

end sub

There's a bit more gubbins in my actual code to give feedback to the user, tidy up things, handle errors etc, but that's the nuts and bolts of it.

if you need to change the queries, just edit the table (or the queries themselves), no need to touch any code.

if you execute a db.querydef object you don't get the "Access is about to insert x records" messages, the dbfailonerror means that the code will break if access encounters a problem.

 

[admessing]

LOL...we meet again....Thanks...will give it a go and let you know how it works.

BTW...how do I get it to delete an existing table first? ie the Coords table that needs to be built before appending to it?

 

[admessing]

Anyone with a solution?

 

[admessing]

Okay....got this in....but....it gives me an error "Compile Error: Do without Loop"....what do I need to add to it?

Also...still waiting for an answer to my previous question...need to delete a table before a new one can be created...How to?

Option Compare Database
Option Explicit
Private Sub Command165_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT_importdata")
 
    Do While Not rs.EOF
        Set qdf = db.QueryDefs(rs![QueryName])
 
        qdf.Execute dbFailOnError
 
        rs.MoveNext
    'Next
 
End Sub

The 'Next is commented out for now...gave me even more idiot warnings.

 

[pbaldy]

For once the error is pretty descriptive. You want "Loop" instead of "Next".

 

[tehNellie]

The man's right, I blame the idiot who posted the original code.

 

[tehNellie]

Also...still waiting for an answer to my previous question...need to delete a table before a new one can be created...How to?

If the structure of the table isn't changing, don't delete it, you lose your indexes, keys etc. Just delete the contents of it and import a new dataset.

query syntax for Deleteing a table, if you really want to delete it is:

DROP TABLE tablename;

This will delete the entire table and all it's contents so be careful when testing it. Access will stop you dropping a table if it has relationships to others.