472,783 Members | 973 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,783 software developers and data experts.

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 8914
32,534 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,534 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,534 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

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

Similar topics

by: Teresa | last post by:
I'm trying to import a tab delimited text file. The text file does not have column heading. I can give it column heading if needed. How do I change the defult comma delimited to tab? If I...
by: oasd | last post by:
I'm having difficulty appending data. I have an import macro (using the Transferspreadsheet function) to import data from an excel spreadsheet (located in a USB attached to the pc) to an access...
by: holdemfoldem | last post by:
Hi. I'm new to this board and have a few questions about using the method referred to in the topic of this message. I have manually transferred over 1/2 million records from a text file into my...
by: Oliver Gabriel | last post by:
Hi, i want to export a table for later import, using vba. That´s my code: export: filename = "C:\HVOtabelle.txt"
by: Chris | last post by:
Hi, I am using the command below for exporting data to text file. One of the query columns has SINGLE number type with decimal ponts as auto. DoCmd.TransferText acExportDelim,...
by: PEB | last post by:
Hi all, Yesterday i've tried this command: DoCmd.TransferText acExportDelim, "Comma_SEP", "myquery", "D:\Temp\Temp.txt" And it gave me an Error that can't find "D:\Temp\Temp.txt" This...
by: ProteusGak | last post by:
Hi there, can anyone tell me how to allow the user to enter in a path on there harddrive for a transfertext item? It works just fine if I set it programmatically, but I really need them to do this,...
by: amitk | last post by:
Hello, I'm using Access 2002-2003. My application exports an Access table into a text file and then appends the text file with several othet files to generate a final file XXXX.ftm . ...
by: Cuaracao | last post by:
This is my code: Code1: 'DoCmd.OpenTable "tblImport" 'DoCmd.TransferDatabase acExport, "dBASE IV", strOutputDir,acTable,"sel_TblImport", strDbfName & ".dbf", False ' DoCmd.Close acTable,...
by: sakurako97 | last post by:
hi, i am trying to find out if it is possible to pass the current recordset of a form to DoCmd.TransferText so i can export it as a .csv I know I can use querydefs etc, but it would be a much...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.