469,306 Members | 2,398 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

DoCmd.TransferText acImportDelim is appending data; I need it overwrite...how to do?

I am using DoCmd.TransferText acImportDelim to import a number of text files. I thought this command would overwrite current table data with new, but instead, it is appending.

How do I get this command to overwrite the current table with new data each month?
Apr 5 '12 #1
5 8306
32,173 Expert Mod 16PB
You can either delete the table itself (slower) or run a SQL job just before the import to clear the table :
Expand|Select|Wrap|Line Numbers
  1. Dim dbVar As DAO.Database
  3. Set dbVar = CurrentDb
  4. Call dbVar.Execute("DELETE FROM [TableName]")
Apr 5 '12 #2

Thank you. Looks like I was wrong; Access DOES append, not overwrite, so will have to use something as you suggest. I am very new to VBA. If I created a list of the tables I need to empty (in another table), can I step down through that list somehow? I thought of creating a CASE statement within an SQL, but again, that means listing a statement for each of the 50 tables I need to clean out before importing. I was hoping to avoid that if possible.
Apr 5 '12 #3
32,173 Expert Mod 16PB
You certainly can, but if you want more specific help then you need to start thinking a bit before you post, and decide what information is required in the question. General questions like this can be answered (and I have) but if you want more direct help, then you'll need to start asking your questions more sensibly (with some thought applied beforehand).
Apr 5 '12 #4
One question led to another...sorry, I did not realize I needed to submit an entirely new post. As I said, I am new to VBA (and to this site). Sorry to bother you.
Apr 6 '12 #5
32,173 Expert Mod 16PB
If you have a question that is essentially different from the original then that is true. If, as in this case, you simply don't have a very sensible question in the first place, then my suggestion was that you put a little more effort into formulating it before posting.

The more your question makes sense, and describes the problem you're actually trying to deal with, the easier it is for us to reply with something that's helpful. Certainly that's something that becomes more obvious with experience, but I don't believe experience is required to appreciate this very basic point. This pertains to your post #3 which is so loosely phrased, and with so little information, that a simple yes or no is all that can be expected in reply. You are not precluded from asking the same question, properly, in the same thread. It is only new questions that rule applies to.

I hope I have clarified the situation as your response seemed to indicate you hadn't understood the point very well.
Apr 6 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by oasd | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.