CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
110SidedHexagon
110SidedHexagon

Reputation: 573

Update data and relationships in Access table from Excel data

So I am importing data from Excel and creating new tables with the data I got from them using the DoCmd.TransferSpreadsheet. What I want to do is have these new tables replace the old tables in my database when I import them while keeping the relationships in-tact so I can completely automate the process with a single button. I don't think the UPDATE query method would work due to the constant adding and taking away of data (though I may be wrong since I am relatively new to SQL). I was hoping there would be a way to just delete all the data out of a table and then fill it back up again from the new tables with VBA. Is this possible?

Upvotes: 0

Views: 865

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

Asuming your tables are named 'T1', 'T2', 'T3' and are in that order for relationships, try the following as one possible solution. Just add this code (changing the table names) following your import code:

Dim strSQL  As String
strSQL = "delete * from T3;"     ' Delete all existing rows
CurrentDb.Execute strSQL
strSQL = "delete * from T2;"
CurrentDb.Execute strSQL
strSQL = "delete * from T1;"
CurrentDb.Execute strSQL

' Insert new rows from imported tables.
strSQL = "INSERT INTO T1 SELECT Import1.* FROM Import1;
CurrentDb.Execute strSQL
strSQL = "INSERT INTO T2 SELECT Import2.* FROM Import2;
CurrentDb.Execute strSQL
strSQL = "INSERT INTO T3 SELECT Import3.* FROM Import3;
CurrentDb.Execute strSQL

Upvotes: 1

Related Questions