MS Access Database with External Data - How should I update Database with monthly delimited data?

Author

How should I update Database with monthly delimited data?

madiziam

2005-11-18, 3:24 am

I have created an access database that has 5 tables of data. Each month an
update will be received. The data will be in txt format and be delimited. I
would like to know the easiest way to update the tables each month. The
tables have queries based on them so I don't want to just replace the tables.
The data will completely replace the existing data which may be easier but
I'm unsure how to do this without a lot of hassle. Could I set up a macro to
make this easier so other people could run the macro and therefore do the
update themselves?

A quick response would be appreciated as I only have a day to do this. thanks

Jeff Boyce

2005-11-18, 7:25 am

Generically, you could:
1. import the data (or link to it)
2. empty out the table (delete query)
3. re-fill with the new data (append query)
4. create either a procedure or a macro that runs the two queries (no
error handling for the macro)

--
Regards

Jeff Boyce
<Office/Access MVP>

"madiziam" < madiziam@discussions
.microsoft.com> wrote in message
news:AF44199A-EB0F-4D93-BF3C- F2114F45AB55@microso
ft.com...
> I have created an access database that has 5 tables of data. Each month
an
> update will be received. The data will be in txt format and be delimited.
I
> would like to know the easiest way to update the tables each month. The
> tables have queries based on them so I don't want to just replace the
tables.
> The data will completely replace the existing data which may be easier
but
> I'm unsure how to do this without a lot of hassle. Could I set up a macro
to
> make this easier so other people could run the macro and therefore do the
> update themselves?
>
> A quick response would be appreciated as I only have a day to do this.
thanks

Douglas J. Steele

2005-11-18, 9:24 am

Another option is to link to the text file, and use an update query that
will update changes to existing rows, or append new rows.

I show how to construct such a query in my November, 2003 "Access Answers"
column in Pinnacle Publication's "Smart Access". You can download the column
(and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jeff Boyce" <JeffBoyce_IF@msn.com- DISCARD_HYPHEN_TO_EN
D> wrote in message
news:e1pEBwD7FHA.1416@TK2MSFTNGP09.phx.gbl...
> Generically, you could:
> 1. import the data (or link to it)
> 2. empty out the table (delete query)
> 3. re-fill with the new data (append query)
> 4. create either a procedure or a macro that runs the two queries (no
> error handling for the macro)
>
> --
> Regards
>
> Jeff Boyce
> <Office/Access MVP>
>
> "madiziam" < madiziam@discussions
.microsoft.com> wrote in message
> news:AF44199A-EB0F-4D93-BF3C- F2114F45AB55@microso
ft.com...
> an
> I
> tables.
> but
> to
> thanks
>

Sponsored Links