Re: Automating a database table update

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ernie (AccessNewbie_at_discussions.microsoft.com)
Date: 08/04/04 

Date: Tue, 3 Aug 2004 17:58:50 -0700

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.

>-----Original Message-----
>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
>
>
>"Ernie" <AccessNewbie@discussions.microsoft.com> wrote in
message
>news:9ec201c4791e$59ec1380$a401280a@phx.gbl...
>> 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
>>
>
>
>.
>

Quantcast