Access 2003 Tables

[Lmens]

Please Help!!

I have a linked table that I’d like to append data from excel spreadsheet every month. Is this possible? For example, I have a linked table (REV–COGS Table) with January data. I will like to add onto this table February’s, March’s April’s…..December’s data by link tables so updates to the monthly spread sheets will be reflected in the REV–COGS Table.

Many thanks for any help you can give me!!

 

[JANR]

Just create an append query and run this monthly.

In the query grid add the linked table of your excel spreadsheet and add the fields you want to append. Then change this to an append query and you'll be promted to which table you want to insert into, select your REV-COGS table and match the fields in this table to your excelsheet.

Just make sure you don't duplicate the data by adding a where-clause to you append query

JR

 

[Lmens]

Thanks JR. Can I use a Macro to delete data from the REV–COGS Table and and re-append to this table when reports are run?

 

[JANR]

Sure.

You can use macroaction RunSQL and use this statement to EMPTY the table:

Delete * From [REV-COGS];

And the second action will be to run the append query

Just use macroaction OpenQuery and specify the name of your append query

This will fist clear the rev-cogs table and then refill it with data.

I'v put square brackets around your table name because of the hyphen, you shoulden't use spaces, hyphens, # or any special characters in objectnames it causes problems.

JR

 

[Lmens]

Many many thanks JR,

Since I'm appending from 12 link tables; one for each month, do I need 12 append queries to fill my table or there is a better way around it?

 

[JANR]

There is always a better approach, but that depends on the external source your linked tables.

If they as structurally the same but just different months then you only need one append query. You can name the linktable tblConnectSource and just relink to a different source prior to running your append query.

JR

 

[Lmens]

Thanks again.