I have a very odd thing happening, it looks like a potenial bug to me in Access 2007.
I am importing 6 tab-delimited text files into 6 pre-existing Access tables. I have set up the Import Specification for each one.
If I manually import each one using the Import Specification I have set up, they all import successfully with no issues.
However, if I try to import them in a macro (using TransferText) or using VBA (also using TransferText), four of the imports are successful, but on the other two, nothing imports. Instead I get "Run-Time Error 3001: Invalid Argument".
I double-checked to make sure that all the arguments are populated properly, which they are (all 6 follow the exact same structure). I even re-built the Import Specifications (and verified that they work manually). Still, no luck. Tried "Compacting and Repairing" the database, nada.
So, I tried another test. I created 6 CSV Import Specifications for the tables, and changed all my Tab-Delimited files to CSV files. Once again, I am able to import all 6 successfully when I import them manually. But, when I try to use TransferText in VBA or a macro, the EXACT same thing happens -- 4 import sucessfully and the same 2 do not import at all, with the same error messages.
I searched around the internet to see if there was any explanation. I found a post where a person describes the EXACT same behavior I am experiencing, but it looks like they never got a solution and resorted to a workaround. That thread can be seen here: http://answers.microsoft.com/en-us/o...0-be75ceec3951
Does anyone have any idea why this might be happening?
BTW, here is what one of the lines of code that is not working looks like:
Code:
DoCmd.TransferText acImportDelim, "CT_Import_CSV", "tbl-CT_Records", myTextCTFileName, False, ""
where myTextCTFileName is the full path and file name of the file (no odd characters or spaces in the file name).
Thanks