Jan 24 2008, 08:38 AM
I have a macro that imports txt files within a holding table. Then I have a query that appends the data from the holding table to my main table. I then am required to delete all the data within the holding table before I am able to import my next days txt files. Question, is there a way to automate the process of deleting all records within the holding table so I do not have to manually delete all the tables prior to importing my next days data?
Jan 24 2008, 08:42 AM
Before the code you're using to import the data, you can use.....
CurrentDb.Execute "DELETE * FROM YourTableName", dbFailOnError
'rest of code that does import
Jan 24 2008, 09:27 AM
I am not sure I understand. I am using a macro to transfer txt files.
Action: Transfer Text
Transfer Type: Import Delimited
Specification Name: """
Table Name: InboundACMRejects
File Name: My path of where the txt files reside along with txt title.
I do see a place for code page. Would I place it there???? And, I don't want all the records deleted until I append them with my query. So, if I added the delete code to my macro import wouldn't that delete my records before I had a chance to append them??
Jan 24 2008, 09:36 AM
First thing I'd suggest is to move away from using macros and use VBA instead. It's immensely more powerful/flexible and importantly you can add error trapping which you can't do with macros (until A2007 anyway). You can convert your macros to VBA automatically (Tools-->Macro-->Convert Macro to VBA). Have a look at the code created by the wizard to understand how it works............
Once you've converted your macro, you just use the code I posted above the import routine. That will delete ALL records in your temp table BEFORE importing new ones. Isn't that what you want to do ?
Jan 24 2008, 09:47 AM
Ok. I will check it out. What I want to do is import into my holding tables, then append the data from the holding tables and then after all the data has been transferred delete the records from the holding table. I used your code behind a button on a form and it works that way also. Thanks for your help.
Jan 24 2008, 10:00 AM
I'd make sure that the temp table is empty before doing your import (you could do it again afterwards if you wanted). It's possible for example that for lots of reasons the table didn't get emptied the last time you run the routine (PC crashed half way through, somebody turned the mains off, the code encountered an error etc etc). If that were to happen, you'd be importing records into your temp table that still contained records, then appending records that you probably don't want to..........
Also, since you're adding and deleting records from a temp table it would be a good idea to Compact and Repair your db on close to make sure that the autonumber (if you're using one) is reset.........
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here