import Excel to Access, replace data, keep relationships

[RsLewis]

I have a mainframe file to load into access table, replacing the current version, but not changing any relationships or querys. Also need to save old version. This is done monthly. I tried rename, but that broke the relationships.
Can I use TransferSpreadsheet to replace all data?

 

[Joe4]

Try using a macro with two lines.

The first line should be the "RunSQL" command with code like:
DELETE [Table1].* FROM [Table1];
to delete all your data from the table.

Then you should be able to use "TransferText" command to import your data into that table.

 

[mdmilner]

Possibly up to 4 steps.

1-Delete contents of Archive table
2-Append Main table to Archive table
3-Delete contents of Main table
4-TransferText into Main table

#1 might be removed if the data is new and you want to continue appending week after week.

Look carefully at specfiles - when manually importing a text file, there is an 'Advanced' button on the bottom left hand corner of the wizard interface. This allows you to create a default specification so that every time it imports, it always knows that you're using spaces/tabs between fields, or perhaps that a certain number of characters represents a given field.

' Append Data to tblArchive
strSQL = "INSERT INTO tblArchive (fld1, fld2, fld3, ...) "
strSQL = strSQL & "SELECT FLD1, FLD2, FLD3, ... "
strSQL = strSQL & "FROM tblMain"
DoCmd.RunSQL strSQL

' Delete All rows from a table
strSQL = "DELETE * FROM tblMain"
DoCmd.RunSQL strSQL

' TransferText command
DoCmd.TransferText acImportFixed, specfile, "tblMain", "C:\folder\file.txt"