Error 3310 (Access 2002/Win XP)

Topic

New Reply

We have an app that does nothing but watch a folder and import the files it finds there. To stress test it, we set it up to repeatedly import a single file, which is actually a zip file containing a series of comma delimited text files, each compatible with a table in the database structure. The import specs are there, and the thing behaves beautifully … For a while. Then suddenly, after it has happily imported the same file several hundred times, it loses its mind and starts throwing a 3310 error, “This property is not supported for external data sources or for databases created with a previous version of Microsoft Jet” for each text file in the archive. Mind you, this is within 60 seconds of having imported the thing before.

After that, NO imports are possible in the database, even from the UI until you close and restart Access. Nothing else has changed, and the database is not overly large. It isn’t an unhandled error somewhere, because the module level and global variables are still populated. I can open the unzipped text files and see the data in them, but Access can no longer import it, not even from the UI. I’m not getting an Out of Memory error or anything else, but I can step through the code and see it break on DoCmd.TransferText. We’re running on XP but I can replicate the behavior on Win2k and it is fairly consistent across machines with different speeds and memory, although the details of *when* it breaks vary slightly.

I have a restart functionality built, shelling out to a restart app, but I want to know what’s going wrong, not just paste a bandage on it. Has anyone else every encountered (and overcome) this?

Reply | Quote

Replies

Sounds to me like there is a limit to the number of text files that can be imported in one Access session. There are limits for everything else.

After you exit Access and restart Access does it bomb after the same number of file imports?

I had an App once that I had to import 3 years worth of history from text files, that was 3 to 4 per day * 5 days per week * 50 weeks in year * 3 years (around 2000 ~ 3000 files) as far as I can recall.

Reply | Quote

Sounds to me like there is a limit to the number of text files that can be imported in one Access session. There are limits for everything else.

After you exit Access and restart Access does it bomb after the same number of file imports?

I had an App once that I had to import 3 years worth of history from text files, that was 3 to 4 per day * 5 days per week * 50 weeks in year * 3 years (around 2000 ~ 3000 files) as far as I can recall.

Reply | Quote

I posted this to the AccessD List for you….

Okay, did a little testing on my own, with Access 97. Used the following code:

Dim i
Do Until i=-1
docmd.TransferText …….
i=i+1
Me.lblStatus.Caption=”Importing: ” & i
‘DoEvents
Loop

Now, as is, above, the code Kicked up an 2051 error when i=11593 (It had imported 46372 records). Stopping the code, compacting the database, repairing the database, nothing would let the code start up again (without immediately kicking up that error message….which said something along the lines of a dialog box was not clicked or something…..), until I closed Access, and opened it up again, then it started right back up.

Next time, it didn’t error, it just hung. It sat at a record level of ~120,000 for about ten minutes, then I just killed it. No error, but it was definitely failing.

Once I remove the quote before DoEvents, so that it would run, it seems to just fly, with no stopping. As I’m typing this, it’s imported the records 1.5 million times and still going.

Here’s my theory. Jet is multi-threaded. I’ve had a little experience with multi-threading VB projects. One thing I know with doing that, is you have to be EXTREMELY careful when accessing a database. In fact, it is almost mandatory to force only one thread to do db transactions. If two threads try db transactions ‘simultaneously’, all sorts of things will happen (from just losing the thread, all the way to corrupting the database….not kidding…). The reason is that Windows isn’t really paying attention to what a thread is doing when it gives time to the next one. So if two threads hit the db at the right time, it can REALLY get goofy.

I’m sure the developers had that in mind when creating the TransferText method, however, they also probably didn’t count on someone importing a file over and over and over, without a break. So what is probably happening, is that you are getting ‘thread lock’, where one or more of Jet’s threads are locked, and there is nothing you can do about it, until you close Access, which will release the Jet threads. Now, I think putting in a DoEvents simply let’s the VBA pause long enough to let the Jet threads finish what they need too, before being called again.

Hope this helps in someway.

Drew

Even though this is Access 97 (don’t have AXP), it is too much of coincidence to run into the same ‘mystery’ type of error. Different number, but obviously doesn’t have anything to do with what is really wrong.

Reply | Quote

Drew,

I already have DoEvents lines in the code for that very reason. I’ve had plenty of experience with Access stepping on itself or getting tangled in other operations, so I took that precaution. There is also a timing loop of 60 seconds between imports, so the process isn’t really continuous. When a file is found, the timer is disabled until the import has finished (i.e., the data is in the tables and the text files have been deleted). At that point, the timer is restarted and the system counts down its wait time until the next check for a file to import.

I also have to say that as far as we know, the behavior of the app is new to Access XP/Jet 4.0. Apparently there are some problems that were reported in A2k and that behaved like a memory leak but were in fact a but in Jet 4. I haven’t found anything linking the problem to 2002, but I don’t think you can truly approximate the situation in A97.

Reply | Quote

Just a WAG!

Have you tried an external trigger, to fire the process from outside? If you application is going to import files that appear in a folder, you can hook into the OS to alert you to new or modified files. To ‘self’ trigger that, you could have your app write a little test ‘temp file’ that triggers the test import…..maybe pushing the process from ‘outside’ might help….

Just another WAG.

Reply | Quote

Just a WAG!

Have you tried an external trigger, to fire the process from outside? If you application is going to import files that appear in a folder, you can hook into the OS to alert you to new or modified files. To ‘self’ trigger that, you could have your app write a little test ‘temp file’ that triggers the test import…..maybe pushing the process from ‘outside’ might help….

Just another WAG.

Reply | Quote

Drew,

I already have DoEvents lines in the code for that very reason. I’ve had plenty of experience with Access stepping on itself or getting tangled in other operations, so I took that precaution. There is also a timing loop of 60 seconds between imports, so the process isn’t really continuous. When a file is found, the timer is disabled until the import has finished (i.e., the data is in the tables and the text files have been deleted). At that point, the timer is restarted and the system counts down its wait time until the next check for a file to import.

I also have to say that as far as we know, the behavior of the app is new to Access XP/Jet 4.0. Apparently there are some problems that were reported in A2k and that behaved like a memory leak but were in fact a but in Jet 4. I haven’t found anything linking the problem to 2002, but I don’t think you can truly approximate the situation in A97.

Reply | Quote