Advanced Question - Data Import Old Table to New Table

[fenhow]

Hi,

I am using the following code to import data from an existing database into a new database as new releases have been distributed.

'Add new tables to Back End
DoCmd.RunSQL "DROP TABLE tblMain;"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbPath, acTable, "tblMain", "tblMain"
DoCmd.RunSQL "DROP TABLE tblDaily;"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbPath, acTable, "tblDaily", "tblDaily"

Up unitl now it has work perfectly every time.

Here is my problem,

On a new release I have added two new columns in the tblDaily. Everything else is the same execpt the two new columns, "Airfare" & "Car Rental" when run the Module that has the above code in it, it runs but the "DROP TABLE" drops my new table and imports the old one thus eliminating the "Airfare and Car Rental" fields.

Can anyone please assist me in getting the SQL right, so when I do the import it adds the old data to the new database keeping the two fields I mentioned?

Thanks in advance.
Fen How

 

[DJkarl]

Hi,

I am using the following code to import data from an existing database into a new database as new releases have been distributed.

'Add new tables to Back End
DoCmd.RunSQL "DROP TABLE tblMain;"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbPath, acTable, "tblMain", "tblMain"
DoCmd.RunSQL "DROP TABLE tblDaily;"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbPath, acTable, "tblDaily", "tblDaily"

Up unitl now it has work perfectly every time.

Here is my problem,

On a new release I have added two new columns in the tblDaily. Everything else is the same execpt the two new columns, "Airfare" & "Car Rental" when run the Module that has the above code in it, it runs but the "DROP TABLE" drops my new table and imports the old one thus eliminating the "Airfare and Car Rental" fields.

Can anyone please assist me in getting the SQL right, so when I do the import it adds the old data to the new database keeping the two fields I mentioned?

Thanks in advance.
Fen How

The DROP TBALE SQL statements aren't deleting data from your tables, they are deleting the ENTIRE table and you are importing a NEW table each day. If you want to keep this method then you will need to add the new columns to the original table you are importing.

The other more conventional method would be to delete the DATA from the local tables using a delete query and run append queries instead of importing the tables.

 

[fenhow]

Thanks,

The problem is this, I have about 30 databases out there that need to be updated, the end user is not equipped (skillset) to add the new columns in their databases. Typically I send out a new database, they run the mod, the old tables are added to the new database and away they go.

Is there any way I can add the new columns in the SQL in the module?

Thanks.

Fen