Run-Time Error 3001: Invalid Argument When Importing in Access 2007

[JoeM]

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:

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

[June7]

Want to provide one of the files for analysis?

[JoeM]

I would have to scrub the data and database a bit, as it contains personal information. It is at the office, and I won't be back there until Monday. If no one has any ideas before then and I don't have it figured out by then, maybe I will try to do that.

I was hoping that it was a problem or known bug that someone may know about (as it appears I am not the only one to have this issue).

[help_me_with_access]

agree with June...there must be a difference that is noticeable in the last 2 files somewhere.

although I've had many issues with visual basic. my latest one, which of course is inexcusable just like the rest, was an issue with the compiler in a vb6 app. either that or the environment itself as a whole. the application became corrupted, probably due to too much use of the compiler breaking due to syntax errors, but still the result was a corrupted application that repeated 1 specific function from top-level coding. it would not advance to the next line of code execution. and on the first repeat it threw me a type casting error. yeah, you figure that one out! =)

my guess here is that its simply an issue with visual basic that you'll never pin down, but I too am interested in seeing the difference between your 1st files and your last 2.

but then again, calling up a saved import schema from visual basic code is probably different than running them manually. i wouldn't be surprised if basic isn't handling the repeated calls well.

if your data is sensitive, just hash some of it and upload it here. wouldn't that be fine? I guess that would get close to violating policy though...

[JoeM]

if your data is sensitive, just hash some of it and upload it here. wouldn't that be fine? I guess that would get close to violating policy though...

Yes, on top of sensitive information, I am dealing with some proprietary stuff as well. Since I kind of like my job, I need to be pretty carfeul here. So I would not only need to dumb down the data, but the database itself. I may try that, I just hope that it doesn't change the situation too much that the problem doesn't exist in the dumbed down version!

Its very odd. I am quite experienced at this stuff. I have been writing VBA code to do imports for about 10 years now, and am used to all the usual pitfalls, but never had something like this happen to me before. Very curious...

[help_me_with_access]

Its very odd. I am quite experienced at this stuff. I have been writing VBA code to do imports for about 10 years now, and am used to all the usual pitfalls, but never had something like this happen to me before. Very curious...

I find that comment so funny, just because of the obvious difference between a language like vba and pioneering languages like C and its extensions. there's no comparison at all. and then of course you have the reasons behind development of every environment.

I still maintain that, even though you're highly experienced in this arena, it boils down to the fact that the visual basic legacy environment is simply an untrustworthy one.

[JoeM]

I still maintain that, even though you're highly experienced in this arena, it boils down to the fact that the visual basic legacy environment is simply an untrustworthy one.

That's funny, in all my years of using Access and VBA, I can't said that I have really had that experience. It usually has been quite dependendable for the projects I have used it for. And we have quite a number of databases in production that have been working quite well for years.

In any event, I have been able to dumb-down the database enough, and reduced it to simply two tables, one that work and one that does not. It would be nice if the issue was that I made some very simple mistake (maybe conflicts with my data validation), but the fact that the Import Specification works when I import the files manually, but does not when the Macro or VBA behind the forum is used makes me skeptical.

The "FI" data imports successfully by macro/code, but the "DP" file does not. So if you try importing all three ways with the Import Specifications that are stored in the database, you should see what I mean ("DP" will not import by Macro or Code, but imports fine when using the Import Specification with a manual file import).

So, feel free to have at it! Hopefully, you will be able to find something that I am unable to.

[JoeM]

OK, after a lot of researching and playing around with it, I finally figured it out.

The breakthrough was when I saw the last post in this thread here: http://www.access-programmers.co.uk/...d.php?t=163828.
In the two tables that were not importing via Macro/VBA, I had fields in each with a Data Type of "Number" and a Format of "Decimal". Apparently, the code doesn't like that (though importing manually has no problem with it). I changed those fields to Data Type "Currency" and Format "Fixed", then re-built the Import Specs, and lo and behold, it works!

Truly bizarre...

[help_me_with_access]

That's funny, in all my years of using Access and VBA, I can't said that I have really had that experience. It usually has been quite dependendable for the projects I have used it for. And we have quite a number of databases in production that have been working quite well for years.

LAUGH. what a difference in opinion! I would hope that I'm the one that's wrong!