Access throws Error 3625 - Saying the text file [Import] specification doesn't exist, but it does.

Papa_Bear1

Member
Local time
Today, 03:59
Joined
Feb 28, 2020
Messages
122
Hi,

I have an import specification, that actually works fine both manually and within a VBA loop (executing a DoCmd.TransferText acLinkDelim command) about 3 times around the loop, but then on the 4th round, it suddenly claims the specification doesn't exist.

My first thought was that Access was somehow deleting the spec, but it isn't. The spec is still there (in the MSysIMEXSpecs table).

So - I'm quite stumped here. I have no idea why it would suddenly think it doesn't exist - which it successfully JUST ran three times in a row.

Anyone else run into this?
Ideas on how to side-step this?
 
Can you provide a minimal sample db to reproduce the issue?

PS: You also should have mentioned that you asked about the same problem before.
Wow - I had done a search but not within this site yet --- and so I didn't happen up on my old post! Yikes... And it was the EXACT same problem... Ugh... Sorry about that...

I'd like to supply code, but I'd have to find time to create that, and I may just give up ---- because ----

One extra tidbit, while I'm here ---
While in debug mode and the error is still occurring, I tried to do a manual import using that same spec. It coughed up the error talking about the Wizard not being installed. Also something that is obviously not true since it ran successfully 3x in that loop just seconds before.

This sure feels like an obscure bug that is possibly/somehow tied to how my MS Office/machine is configured (not up to me...). So, the likelihood of MS either fixing the bug, or someone in IT actually solving it is probably mathematically zero. This likely means I'll just have to abandon this approach - or so it seems to me.
 
Well, first thing to try is another computer?
Are you sure the spec name is not being amended somehow, or is it harcoded?
 
In this loop, by any chance do you have a section in a subroutine for which you have an ON ERROR RESUME NEXT ?? Do you have a section in a sub for which you do a DoCmd.SetWarnings FALSE? (Or 0?)
 
I would create a query with this SQL to show all your import/export specs
SQL:
SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.FieldName,
MSysIMEXColumns.DataType, MSysIMEXColumns.IndexType,
MSysIMEXColumns.Start, MSysIMEXColumns.Width
FROM MSysIMEXColumns INNER JOIN MSysIMEXSpecs
  ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID;
 
I'd like to supply code, but I'd have to find time to create that,
You didn’t last time either. So you are happy for us to spend time on your behalf guessing what the issue is?

Only suggestion I can make is there is an issue with the file you are trying to import- perhaps too big or corrupt? Have you tried skipping that file?

I’m not sure your tidbit is a valid test if the code is still running even if paused due to an error
 
What line of code displays the error? Can you not display the spec that it's trying to use to make sure you understand the error?
 
So, the likelihood of MS either fixing the bug, [...] is probably mathematically zero.
That was one of the reasons I asked for a simple reproducible example of the problem.
If you just report an obscure problem with an error occurring for some of thousands of files and only include a vague description of your code, the chances of Microsoft fixing this are indeed zero.
However, if you could provide an reproducible example and the error also occurs outside of your environment, it is a problem that Microsoft can review and consider fixing. The chances for a fix will depend on the exact scenario that triggers the error and the effort required to fix the problem. - Based on your description of the problem, I don't think the chances are good, but they are definitely greater than zero.

The other reason is: If you upload an example here, knowledgeable people can review it and maybe point out an error in your code or, if it is a bug, a sensible workaround for the problem.
 
In this loop, by any chance do you have a section in a subroutine for which you have an ON ERROR RESUME NEXT ?? Do you have a section in a sub for which you do a DoCmd.SetWarnings FALSE? (Or 0?)
In this case - no - I don't have any Resume Next stuff happening, and also no SetWarnings False either (as far as I know... ;-)
 
I would create a query with this SQL to show all your import/export specs
SQL:
SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.FieldName,
MSysIMEXColumns.DataType, MSysIMEXColumns.IndexType,
MSysIMEXColumns.Start, MSysIMEXColumns.Width
FROM MSysIMEXColumns INNER JOIN MSysIMEXSpecs
  ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID;
I did look at the MSysIMEXSpecs, and it is there. In fact, if I were to restart the system and run it again - it seems it gets through three iterations - using that spec successfully, and then fails. So, it very likely isn't about the spec existing or being referenced properly etc.
 
You didn’t last time either. So you are happy for us to spend time on your behalf guessing what the issue is?

Only suggestion I can make is there is an issue with the file you are trying to import- perhaps too big or corrupt? Have you tried skipping that file?

I’m not sure your tidbit is a valid test if the code is still running even if paused due to an error
I understand the general principle of supplying code - but in this case, I just can't see how that matters much. I did describe what I'm doing - using TransferText, with an import spec, inside a loop, that works three times in a loop and then suddenly doesn't. Interesting point on the file size, as they are big, but, they are all big (order of 50MB or so). So, again, nothing special about the 4th one - unless something is accruing and that is why it takes three times to fail. Interesting.
 
What line of code displays the error? Can you not display the spec that it's trying to use to make sure you understand the error?
It is the TransferText command. I can indeed display the spec - in the system table, at any time. It exists before, during, and after the code runs.
 
That was one of the reasons I asked for a simple reproducible example of the problem.
If you just report an obscure problem with an error occurring for some of thousands of files and only include a vague description of your code, the chances of Microsoft fixing this are indeed zero.
However, if you could provide an reproducible example and the error also occurs outside of your environment, it is a problem that Microsoft can review and consider fixing. The chances for a fix will depend on the exact scenario that triggers the error and the effort required to fix the problem. - Based on your description of the problem, I don't think the chances are good, but they are definitely greater than zero.

The other reason is: If you upload an example here, knowledgeable people can review it and maybe point out an error in your code or, if it is a bug, a sensible workaround for the problem.
Got it.
 
Indeed - I will try that today!
Create a DB with just what is needed to see the issue. Compact and upload that. Zip it if needed also.
Then you will be able to test on several computers and different versions of Access.
 
Indeed - I will try that today!
OK - tried this on a non-corporate-managed computer - and it does the same thing. I admit I'm quite surprised.

Either the other computer has something similarly configured - or this is an actual bug of some kind (whether my code or Access).

If I can find time, I'll try to whip up a simple DB file with this operation in it to upload. I probably can't supply the data files being read in - as they are over ~60 MB each. They are XYZ files generated by a GDAL routine using geotif input files as the basis, to give me easy access to the terrain data.

Here is the gist of it though...
Basically I have an array that tells me the names of available topographic data files, and my goal is to rifle through the related XYZ files and pull the terrain data into an array for later processing...

Code:
    For lngFileRowLoop = 1 To intNbrFileRows
        For lngFileColLoop = 1 To intNbrFileCols
            strTopoPathFile = Nz(parrFiles(lngFileColLoop, lngFileRowLoop), "")
            strTopoPath = Left(strTopoPathFile, InStrRev(strTopoPathFile, "\", -1, vbTextCompare))
            strTopoFile = Right(strTopoPathFile, Len(strTopoPathFile) - Len(strTopoPath))
            strTopoXYZFile = Replace(strTopoFile, ".tif", "_XYZ.txt")
...
<<Code that allows me to try different methods - this is just one of them.>>
...
                    If TableExists("XYZ_Imported") Then
                        DoCmd.DeleteObject acTable, "XYZ_Imported"
                    End If
                    DoCmd.TransferText acLinkDelim, "MyXYZTextImportSpec", "XYZ_Imported", strTopoPath & strTopoXYZFile, False
                    Set rstTopoData = CurrentDb.OpenRecordset("XYZ_Imported")
                    rstTopoData.MoveLast
                    rstTopoData.MoveFirst
                    lngNbrRecs = rstTopoData.RecordCount
...
<<Code that pulls the data and stuffs it into an array.>>
...
        Next
    Next
 
Last edited:
In this case - no - I don't have any Resume Next stuff happening, and also no SetWarnings False either (as far as I know... ;-)

The reason I asked is because if this fails in a loop, there is always the question of "What happened at the end of the previous loop (that appeared to have worked)?" Followed by "What happened in the beginning of the current loop (that then failed)?" The presence of two apparently completely normal iterations of your loop suggests that something totally anomalous occurred in one of the two places I just mentioned.

Since you are looking at a loop involving a TransferText (which is a "closed" action that you can't single-step into), it SHOULD be possible to run this with breakpoints set so you can see what happens. You can perhaps breakpoint your way around your "stuff it into an array" segment but you need to see what happens in some detail. For instance, you have an "if table exists, delete it first" sequence in your example. If there are other IF-THEN blocks and some of them don't execute in the first two loops, they become candidates for the locus of the error. And the only way to know what actually happens is via debugging with single-step.
 
Does it make a difference if you change the order of the files in the processing cue?
 

Users who are viewing this thread

Back
Top Bottom