Replacing Tables

[BWolf]

Here is the situation:

I have a front-end and back-end to a database that I maintain. I want
to leave the database up to someone else and I want to automate the
daily updates that I do so that its minimal work. Can I write a macro
(or VBA code) that would do these steps:

1. Delete old tables
2. Import tables (xls)
3. Define primary keys (some talbes have multiple keys)
4. Recreate relationships

Is this possible?

Thanks for any help
Brad

 

[KARL DEWEY]

Probably the easier thing to do is to link the Excel files and build macro to
update your tables.

 

[BWolf]

I thought about this, but the excel files are created daily, so this is
just as much work as doing it all manually.

 

[KARL DEWEY]

Not if they keep using the same name and it resides in the same place.

 

[Tim Ferguson]

1. Delete old tables

No: just empty them. Vis

DELETE FROM oldTable WHERE TRUE;

(the Where True is not necessary, but is a reminder that this is a very
destructive operation!)

2. Import tables (xls)

Just run an append query into the empty tables

INSERT INTO oldTable (OneField, TwoField, ThreeField)
SELECT FieldOne, FieldTwo, FieldThree
FROM [Sheet1$] IN "" [excel5.0;database="\\server\drive\database.xls"

(the IN clause syntax is taken from memory: you'll have to check the
appropriate strings for your own needs. It's all documented in the help
files.)

3. Define primary keys (some talbes have multiple keys)

Don't need to if you don't destroy the tables in the firsst place

4. Recreate relationships

Ditto


Hope that helps



Tim F