Why do I get an empty record imported for every actual record that I import from a txt file using my Access 2003 application with an Import Specification?
I created the barest of my application in Access 2003 using an MDB type file.
I developed an Import specification to import a txt file.
I have attached a sample input txt file and my Access 2003 application.
Do you know why I get empty records with the 2 input records?
For every input record with values that I import , I get an empty record as well.
So if I were to import 100 records from a txt file, I would get 200 records in the Access table (100 records with values plus 100 empty records).
The VBA code is as follows:
Private Sub cmdImport_Click()
On Error Resume Next
DoCmd.TransferText acImportDelim, "IMNYCashBreaks Import Specification1", "OI_Intell_Mod", "U:\Dec20\IMNYCashBreaks.t
MsgBox ("File has been transfered successfully!")
End Sub
IMNYCashBreaks.txt
Test.mdb
I developed an Import specification to import a txt file.
I have attached a sample input txt file and my Access 2003 application.
Do you know why I get empty records with the 2 input records?
For every input record with values that I import , I get an empty record as well.
So if I were to import 100 records from a txt file, I would get 200 records in the Access table (100 records with values plus 100 empty records).
The VBA code is as follows:
Private Sub cmdImport_Click()
On Error Resume Next
DoCmd.TransferText acImportDelim, "IMNYCashBreaks Import Specification1", "OI_Intell_Mod", "U:\Dec20\IMNYCashBreaks.t
MsgBox ("File has been transfered successfully!")
End Sub
IMNYCashBreaks.txt
Test.mdb
ASKER
The file you sent works fine. It creates only the 2 records with values.
Do you know how I could programtically remove the extra returns after each record (to accomplish what you did) using Access 2003 VBA code?
This is only a staging table but I import over 60,000 records from a txt file so I end up with over 120,000 records.
I wish I could clean up the txt file with the extra return before the import runs and creates all the wasted space in the import staging table.
Do you know how I could programtically remove the extra returns after each record (to accomplish what you did) using Access 2003 VBA code?
This is only a staging table but I import over 60,000 records from a txt file so I end up with over 120,000 records.
I wish I could clean up the txt file with the extra return before the import runs and creates all the wasted space in the import staging table.
ASKER
My users tends to keep the Access mdb file open for days creating an LDB file and thus the application is sometimes locked for up to 1 week before I can compress the application over the weekend, once the lock is finally removed. And they import the txt file daily. The mdb file may be about 139,000 kb on Monday and by Friday it is over 1,000,000 kb. Then I can compress it over the weekend and get it back down to a 139,000 kb file once again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IMNYCashBreaks2.txt