Updating Imported Tables on a Regular Basis

[duluter]

Here's my scenario:

I have an Access database into which I import data from three DBF files on a regular basis. I get the DBF files from a third party and I replace my existing data with the updated data when I get them. I import the files as three separate tables, call them tblA, tblB, and tblC (one from each DBF). I want to get rid of all the data in each of these three tables every time I receive the DBF updates from the third party. Let us assume that I must import the data, rather than link to the DBFs--external links are unacceptable in this scenario.

Is there an easier way to update my data than to delete the three existing tables out of my Access database and then import the new DBFs one at a time? I'd prefer to have it be more automated than that. All the fields will always be the same.


Duluter

 

[WayPay]

Something like this ?

DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE * FROM tblA"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\a.dbf", acTable, "", "tblA", False
DoCmd.RunSQL "DELETE * FROM tblB"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\b.dbf", acTable, "", "tblB", False
DoCmd.RunSQL "DELETE * FROM tblC"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\c.dbf", acTable, "", "tblC", False
DoCmd.SetWarnings True

 

[duluter]

Wow. Yes. That is exactly what I need.


Thanks a million.

Duluter

 

[boblarson]

I would advise either:

1. NOT using DoCmd.SetWarnings and the DoCmd.RunSQL but instead use
CurrentDb.Execute in its place.

2. If you feel you must use DoCmd.SetWarnings then you need to make sure to have an error handler in that procedure and the very first item in that error handler should be

DoCmd.SetWarnings True

otherwise you may find yourself without warnings if something "glitches" and fails to work properly and doesn't get to the normal code which turns them back on.

You'll find that most Access MVP's will counsel to use the CurrentDb.Execute method instead of DoCmd.RunSQL for the reason that it does not require you to do anything but running of the SQL.

 

[duluter]

@boblarson:
So is this what the code would look like?

CurrentDb.Execute "DELETE * FROM tblA"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\a.dbf", acTable, "", "tblA", False
CurrentDb.Execute "DELETE * FROM tblB"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\b.dbf", acTable, "", "tblB", False
CurrentDb.Execute "DELETE * FROM tblC"
DoCmd.TransferDatabase acImport, "dBase IV", "c:\my\c.dbf", acTable, "", "tblB", False


?

Duluter

 

[duluter]

OK, jumping from the general to the specific, here's the code I tried:

CurrentDb.Execute "DELETE * FROM tblRareFindELM"
DoCmd.TransferDatabase acImport, "dBase IV", "C:\Resources\Databases", acTable, "ELM", "tblRareFindELM", False
CurrentDb.Execute "DELETE * FROM tblRareFindFED"
DoCmd.TransferDatabase acImport, "dBase IV", "C:\Resources\Databases", acTable, "FED", "tblRareFindFED", False
CurrentDb.Execute "DELETE * FROM tblRareFindCAL"
DoCmd.TransferDatabase acImport, "dBase IV", "C:\Resources\Databases", acTable, "CAL", "tblRareFindCAL", False


Instead of writing the new data to the tblRareFindELM, tblRareFindFED, and tblRareFindCAL tables, it created 3 new tables:tblRareFindELM1, tblRareFindFED1, and tblRareFindCAL1.

Any suggestions?


Duluter

 

[WayPay]

You'll find that most Access MVP's will counsel to use the CurrentDb.Execute method instead of DoCmd.RunSQL for the reason that it does not require you to do anything but running of the SQL.

Good advice; I'll change my programming style accordingly. Always happy to learn something new & improved .