TransferText Error

[Frothingslosh]

Okay, I'm trying to help a co-worker resolve an error he's encountering, and failing miserably.

The idea here that the user enters a month/date value into a text field and hits a button, and the system imports a text file that matches the entered value into a specific table.

Here is the code in question (double single quotes are used for the path because the site will not allow me to enclose the path without turning it into a hyperlink):

Private Sub cmdImportDispositions_Click()
Dim FilePath As String
    FilePath = ''\\snt201\AAFA\'' & Me.txtMMYY.Value & "DISP.txt"
    DoCmd.TransferText acImportDelim, "Check Disp Import Specification", "Dispositions", FilePath, True
End Sub

This code invariably results in 'Run-time error '3001': Invalid argument.

Observations and notes:

1. The import specification 'Check Disp Import Specification' exists.
2. The import specification works perfectly when run manually.
3. There are no fields added to the import specification that do not exist in the source text file.
4. The field names listed in the specification match the field names in the destination table perfectly.
5. The table 'Dispositions' exists, and the name is spelled correctly.
6. The source data has a header line. I have attempted:
   1. Header row indicated in both specification and VBA
   2. Header row indicated in spec but not VBA
   3. Header row indicated in VBA but not spec
   4. Header row not indicated in either VBA or spec
7. The file path resolves correctly. A different error entirely is generated when there is no matching file.
8. There are 5 other command buttons that do a text import.
   1. Four of them have been in the app since the start.
   2. One of them is another new text import. This one had the same error, but the 5th time I re-created the same spec, it started working. Just to test, I re-created the spec 6 more times, and got a working spec executable by VBA precisely once more. I DID NOTHING DIFFERENTLY.
9. Each different file type being imported has a drastically different structure, so re-using a spec is not an option.
10. I have already spent an hour going through Google search results for this error, including a half-dozen different threads on this forum. None have helped me resolve this.

Anyway, any help would be much appreciated.

 

[sneuberg]

Since this says all of the arguments are optional I suggest leaving them out one by one to find out which one is causing problems.

If you can upload that part of the database, i.e., at least the table, spec, and a sample text file I'll see if I can figure it out.

 

[Frothingslosh]

Since this says all of the arguments are optional I suggest leaving them out one by one to find out which one is causing problems.

If you can upload that part of the database, i.e., at least the table, spec, and a sample text file I'll see if I can figure it out.

A couple problems there:

• I've already checked each argument individually and collectively. For an import, the source file and destination table are mandatory, not using a saved specification causes it to look for a field with a name matching all column names in the source text concatenated together, and the First Line Is a Header parameter has no effect on the error message.
• I've even gone so far as to attempt to import the data into a table with nothing but text fields and which has no fields that are not in the source file. No joy.
• I'll try to get a stripped copy uploaded. The original is 100% pure, unadulterated HIPAA-protected PHI, so I have to be really, REALLY careful.

 

[The_Doc_Man]

Guess I'll be Captain Obvious.

Which line is highlighted when it barfs? The "DoCmd.TransferText" line?

If so, use the immediate window to see the value in FilePath when it breaks. No other item on that line looks questionable.

I suspect the quote-marks are your culprits. Having had headaches before with this kind of thing, I might have tried this:

...
stBkSlsh = "\"
...
FilePath = stBkSlsh & stBkSlsh & "snt201" & stBkSlsh & "AAFA'' & stBkSlsh & Me.txtMMYY.Value & "DISP.txt"
...

Escaping a special character is such a pain in the toches that I have developed a habit of using the above method. That way, I have no doubt regarding what I actually put into the string.

 

[Frothingslosh]

Actually, the file path works fine. The same format is used in a working function on the same form that is also included. I've also tried with a purely hard-coded path with no success.

I'm attaching a stripped database. The path in the code will obviously need to be updated.

Oddly, the CR specification works perfectly, only the DISP one doesn't, no matter how many times I remake it or try to tweak it.

 

[sneuberg]

I was able to import the file when I made a spec with all of the fields as short text so I expect there may be a type mismatch going on somewhere. I'll try making a spec like the one causing the problem now.

 

[sneuberg]

Not that I think it's the problem but why does the CheckDispositions table have a field named RunDate that doesn't exist in the text file?

 

[sneuberg]

While I haven't been able to figure out the cause of the error I suspect that the specification is the culprit as I've been able to create one that works in the the attached database. The one I made is named CheckDispostionsSpecification and if you compare it to yours by running the qrySpec I don't see any difference so I can't explain why. Nonetheless you can see that CheckDispostionsSpecification works with the table named Copy Of CheckDispositions which is different from CheckDispositions only in that the field names have been changed to match the field names in the text file. I got an error about the field names when they didn't match but it wasn't the error about the argument.

Hope this info helps.

 

[The_Doc_Man]

Steve's observation and your earlier comment that it works OK manually lead to the question: Does the target table already exist AND if you try to import to a different name (so that it has to use an auto-created table), do the field types match between the pre-fab and the auto-gen tables?

 

[Frothingslosh]

Target table already exists.

Attempt to create a new table ALSO generated the 'Invalid Parameter' error, so there are no field types to match.

Keep in mind that the CR import had the same issue as I'm having with the DISC import, and then for no discernible reason, my 6th try suddenly worked when used with TransferText. Then in attempting to nail the issue down, it required 6 *MORE* attempts to generate an import specification that worked. There is was no discernible difference between any of the specifications.

There are two records with dates of 1/0/00 in the CR file (and one in the DISP file), but in the successful imports of 1215CR, those dates are simply replaced with nulls. In the unsuccessful imports, I couldn't even import them into text fields.

I checked all 15,000 lines of the true CR file and all 25,000 lines of the DISP file in excel, and not a single field should have a conversion problem other than the bad date-to-null issue I mentioned. Additionally, only one field had more than 20 characters of data, and that field is a size 75 text field, which is actually larger than it has in the source database. I also did not find any special or non-English characters that would cause Access to choke, so there was no reason an import into a staging table consisting of nothing but size-255 text fields should fail.

The fact that you ran the import fine when my copy can't is really starting to suggest corruption to me. That won't be fun - while the database literally only has two forms, what it is is a data storage tool for a data analyst who insisted (long before I started here) on doing her own querying for reports. That means that it has approximately 50 supplementary tables, over 150 queries, and around 30 saved reports. (I would love to turn it into an actual locked application, but no way in hell can I justify that kind of effort and time to my boss for a one-user application.)

Anyway, I may have to just do the new-database-and-import-everything stunt and see if that works when I get back to work on Wednesday.

Thanks for looking into it!

 

[Frothingslosh]

Not that I think it's the problem but why does the CheckDispositions table have a field named RunDate that doesn't exist in the text file?

Run date is an attribute that will be added in a second step. Basically, the CR and DISP files are monthly reports that can have identical lines, so that field will allow the user to differentiate between each month's files. 'RunDate' was used as the identifier at the end-user's request.

If you check the Check Request file and spec (which works), the same situation exists there.

 

[The_Doc_Man]

Good luck on the mass import operation!

I have to concur that something must be corrupted. It doesn't get this persistent otherwise.

 

[JHB]

The problem is one of the field in the specification is missing the "Data Type".
For getting access to an already created specification you've to create a new specification and click the "Advance" when the import wizard open, then follow the instruction in the picture.
It is really space for improvement for the Microsoft group, it is a pity you can't get direct access to a created specification.
When you run the specification after the change you'll get another error, but that is related to a missing field in the insert table, I think you don't have any problem to solved that error.
But all in all, the error message was correct - empty argument = Invalid argument!

 

[Frothingslosh]

Oh, I know how to open an existing specification, I spent WAAAY too much time playing with them Friday. Hell, at this point I even know how Access saves all the specification information in the hidden system files and can probably create one by hand that way.

That said:

!!!

I'll be checking the actual database when I get back to work on Wednesday, but assuming what you said holds there, you just saved me a ton of work!

 

[sneuberg]

@JHB

Good catch. I tried that and that solves the problem but it's still a mystery to me how that could have happened unless some sort of corruption is going on. The screen in the wizard on which you specify data types doesn't allow you to leave the Data Type blank.

 

[JHB]

... The screen in the wizard on which you specify data types doesn't allow you to leave the Data Type blank.

By me it does, MS-Access 2010.

 

[JHB]

.. and can probably create one by hand that way.
..

Some of the fields are locked.

 

[Frothingslosh]

Ok, I just checked on the live database, and that was the problem, JHB. Thanks!

 

[JHB]

You're welcome, good you got the problem solved.

 

[Frothingslosh]

Oh, for anyone following this:

The problem fields (the ones that were not saving a Data Type) all had the same thing in common: They were all numeric fields set to Decimal. Apparently the wizard doesn't play well with those.