Questions about setting up batch queries

[T. H. Ormond]

I need to set up some queries to process tables in batch mode. By this I
mean I want to create a set of SQL statements which create some new tables,
update those tables, and then programmatically edit several of the fields.
I had some procedures set up using another database system ( MYSQL ) which
allowed me to package one or more SQL statements in text files. Then I
could create command procedures to invoke MYSQL with the desired SQL
statement as input. I am trying to learn how to do this same sort of
processing in ACCESS.

What is the best way to set up a series of command procedures ( invoked by
shortcuts from the desktop for instance ) which can be run automatically
ie by double-clicking on the icons ) rather than running ACCESS and
selecting various queries to be run. Using MYSQL it was possible to set up
a series of command procedures to do the work with a minimum of user
interaction. This is what I would like to do with ACCESS. Any suggestions
are welcome!!

Thanks in advance,

Tom Ormond

 

[Albert D. Kallal]

Just build the series of queries in the query builder and save them.

You can then execute them with code.


currentdb.Execute "query2"
currentdb.Execute "query3"


You can even write code in a module where you put the sql in-line. Not
always considered the best practice, but I certainly do this quite often.

currentdb.Execute "update tblCustomers set City = 'New York' where City =
'NY'"

If you are using in-line sql, then I would at first use:

If you want some error checking for the in-line sql, then you should use:

docmd.RunSql "update ......etc etc"

 

[T. H. Ormond]

Thanks for your pointers on doing queries. I played around with some macros
but I have read and heard from various sources that it is better to use VBA
than macros. I see how your code will work in conjunction with predefined
queries, but I do not yet see how to package the queries into something that
I can run from a shortcut or via the Windows command line. All of the books
I have read talk about creating functions or procedures which can be
attached to various events which are experienced by UI components of one
sort or another.

Do I have to build some sort of form in order to utilize VBA code, or can I
build a more free-standing executable? I have seen a macro action which
will allow me to run a VBA procedure. Is that the way I have to go??

Thanks again for your help in this matter!

Tom Ormond

 

[Albert D. Kallal]

Do I have to build some sort of form in order to utilize VBA code, or can
I

build a more free-standing executable? I have seen a macro action which
will allow me to run a VBA procedure. Is that the way I have to go??

Yes, you kind have to. You certainly create a mdb that runs code on start-up
(as you mentioned).

If you fire up a dos window, and type:

start yourAccessBatch.mdb

Then code be run, and just make sure the application exits after being done.
You thus can use the scheduler to launch the above. I find it easer to just
a start a form that runs at start-up.

However, you can just place code in a standard module, and have that code
run at start-up via making ONE macro called:

AutoExec

In side the above macro, you use:

RunCode

And simply specify the name of the function in the module to run. Just
remember to have the code quit the application after.