Automating a database table update

[Ernie]

I have an access 2k project which extracts selected
information from a 3rd party system using odbc. The whole
reason for this project is to avoid paying the 3rd party
developers mucho moola to code the needed reports directly.

What I need to do is get this project to automatically
update the 6 tables by running a series of 17 queries.

Normally, you would use the odbc link manager to gain
access to the live tables and select the data that you
need. The problem is that this odbc link is so clunky that
it literally can take days to run a complex query on just
two of the tables in the 3rd party db. That is not an
option here.

What I've done for manual updates is use File / Get
External Data / Import to copy each of the files that I
need into a temporary database. Then run make-table
queries to pare down these huge tables to just the subset
of data that I need and write those tables into my access
project database. Doing this manually takes about 3 hours
and only gets me the base tables, not the finished work
that I need for the project.

Once I have the base tables, I start running the series of
17 queries to finish off the 6 tables on which the project
is based. Since I've already pared down the original
tables to the subset of data that I need, it only takes
another half hour to do the finish work.

There are no changes made to the data, it's just a matter
of selecting information from several tables, in 1:1, 1:N,
and N:N relationships and formatting it to fit on my forms
and reports.

Now what I would really like to do is set up macros or
modules or something that can be run automatically once
week or 2-3 times per month to update these tables for me.

I have no clue how to make or use a macro and the help
files on macros in access are no help to me.

Could someone grab my nose ring and lead me through this
step by step please?? Or at least rent me a clue on where
to start.

Thanks in advance,
Ernie

 

[tina]

well, i cringe at the idea of teaching you how to use macros in a newsgroup
post. suggest you get a book to teach you the basic concepts; i can
recommend the Microsoft Access <version> Bible by Prague and Irwin.
from your explanation, sounds like you are "redesigning the wheel" each time
you go thru this process. if the source of your data does not change from
one "time" to the next, suggest you do the import, then use append queries
(instead of make-table queries) to dump the data into the already existing
destination tables. the value of this is that all subsequent queries are
already built and tested, as are the forms and reports - based on those
"already existing" destination tables. in other words, after your initial
setup of the import specifications, import tables, destination tables and
all queries, forms and reports, you should never have to change the design
of those objects again (unless a data source changes, or you need to
view/report the data differently). once you have the individual steps
standardized, it should be easy to read up on creating macros and create one
(or more) that will run all the steps one-after-the-other, at the click of a
button.

hth

 

[Dan Eveland]

You can use the "Scheduled Tasks" in the control panel to set the times you
want it to run, then envoke Access through the command line, setting your
kick-off macro to run on startup by naming it "Autoexec".

Dan

 

[Ernie]

Thank you for your reply. I've actually done all that you
suggest (including reading up on macros in the access 2000
bible by Prague and Irwin). The data on which this project
is based changes constantly. Append queries won't update
existing records and update queries would be too complex
to get all of the 60 or so fields (spread over the 6 final
tables) to update. Because of this, I chose to replace the
final tables (make table queries) instead of trying to
update them.

The final forms and reports are based on the final 6
tables and are not affected at all by ripping out an old
copy of one of these tables and creating a new one in its
place. These are limited to one user which can be asked to
log out for the update.

In either event my problem is not with the final
forms/reports/queries but with the series of queries that
I have to run to pull just the selected subset of the
original data which are then fed into the final forms etc.


Too give you a grasp of the scope of this project, the 3rd
party database consists of some 230 tables (including
separate indexes for many of them) comprising some 60
Gigabytes of data, with the largest of these tables having
well over 5 million records. Of this, I need to select a
subset of data which is unlikely to exceed 300 Mb in just
6 tables. Of the 230 tables, I need data from just 25 but
I need a piece here and a byte there. And as I stated
previously, the odbc link is too clunky to run a link
manager, so the needed tables have to be copied to access
and pared down from there.

I will keep poking away at this, eventually I will figure
out how to automate the process.

And thank you Dan, but until I figure out how to create
and run the macros that I need, scheduling them won't be
an option.

 

[tina]

well, deleting all records from a table and appending new records
accomplishes the same end result as deleting the table and recreating it
with a make-table query. as to which is "better", i couldn't say - perhaps
it's a wash.
if you've read the Macros section of the Access Bible, then you've been
exposed to the basics. what specifically are you having trouble with re your
macro?

 

[Guest]

I am having the same problem and I can get the macro to run but it prompts
the user to click yes to deleting the tables which freezes the macro if there
is no user available. I want to eliminate the prompts. Have you figured it
out yet?
Thanks, Jeannethe

 

[Douglas J. Steele]

Try using VBA instead. You get better control over things like that.