Forum Discussion
Windows or Office 365 Updates on 7/12/22 Introduced Error with TransferText in vba for Access
After the above noted updates I have received several reports of Access error 3125. I have traced this issue to a saved import specification. Below is an example of the error when using an import specification and an example without the import specification that does not error out.
The original code that has run for years with no problems is:
DoCmd.TransferText acImportDelim, "OSResponses", "RetestResponses", "Scan.txt"
My diagnostics code and the error are below:
The modified code that does NOT throw the error is:
DoCmd.TransferText acImportDelim, , "TestTable", "Scan2.txt"
Here is a screen shot of the MSysIMEXSpecs table:
16 Replies
I think I found the origin of this problem.
In my case, it occurs when the file type is not specified in the import specification.
If file type is 0 in MSysIMEXSpecs, the problem will occur.
As soon as it is set, the problem disappears.
BestUnfortunately, the FileType was already zero and it still does not work. I've removed TransferText from all my apps and will never use it again. Thanks for the tip though, it did get me into reading more about the MSysIMEXSpecs table.
What I'm saying is that it shouldn't be 0 but a real filetype value (850 for example).
If you use the import wizard and save the format, the type must be filled in, which is not the case when it is 0.
- There have been other reports of bugs introduced in the most recent Windows Update, primarily associated with Security issues. This, being an automation procedure, might be related. I don't know any more details yet, though. One thing I'd verify, though, is that the folder where you are trying to export the file is designated as a Trusted Location for Office. Also, fully qualifying the path, as Arnel already suggested, is always a good idea.
- From three more hours of diagnostics, the TransferText feature is well and truly broken so if you have those in your apps you may want to check to see if they are functioning
My findings are that if you have a static file location (any name, any path) and do a manual import to the destination table, the TransferText feature will then work. And, that has to be done on each individual user's copy of the application and should the file location change, it's busted again. For obvious reasons, this is not a workable solution. My work-around is to never use TransferText and read records in from the text file (my app lets the user choose the file location):
Open CurDir & "\Scan.txt" For Input As #1
'Get a record
While Not EOF(1)
Input #1, vID, vRec
vsql = "INSERT INTO RetestResponses(StudentID, Responses) VALUES (" & CStr(vID) & Chr(44) & Chr(34) & vRec & Chr(34) & ");"
Wend
Close #1- it is much faster if you just Open a Recordset and Append record to it than using Insert Query.
- can you fully qualify the text filename with the Correct Path, eg:
C:\theFolder\Scan2.txt- I've tried that with the same results. The only thing that throws the error is using a saved specification. Thanks for the suggestion.
- Oops, Edit to previous post - won't do anything without the RunSQL :
Open CurDir & "\Scan.txt" For Input As #1
'Get a record
While Not EOF(1)
Input #1, vID, vRec
vsql = "INSERT INTO RetestResponses(StudentID, Responses) VALUES (" & CStr(vID) & Chr(44) & Chr(34) & vRec & Chr(34) & ");"
DoCmd.RunSQL vsql
Wend
Close #1
