MS Access 2003 - System Resources Exceeded Message When Importing Data

[Rogiecrockett]

I am new to this site. I have searched and found similar posts but don't think they are answering my question.

I have a basic and simple DB. 4,170,000 records each record being 6 fields. File size is 1 Gb.

Monthly I have our I/T person provide me a txt file. I then use an Import Spec and import the data into my table. Over the last 4 months, I have run into a problem where after I OK the Import Spec and go to make the import, I get the nasty message from the Import Text Wizard dialog box -- "System resource exceeded." I then get a message that says an error occured and the file was not imported. I also get this message if I don't use the Import Spec but just make the import manually - renaming the fields every time I reattempt the import. There is never any more error info other than the message above.

What is aggravating is that until a few months ago I could make this work. Also when I have our I/T person help, she can make it work on her computer using the same methodology. Also, I can make it work on my desktop at home. All of this leads me to believe there is a setting for either Windows or some other system that is preventing this from working.

Other facts -- I get this error when Access is the only software running. We added more memory, I believe it is now approx 1.5 Gb.

Application name Microsoft Access
Version 11.0
Build 5614
Language English (United States)
VBA version 6.04

I have added some software but nothing DB related. I don't have a way of going back in time to know what the settings were prior to this problem.

Any ideas where to start my search or what I can zero in on to check???????

Thanks for any help anyone can provide.

Dave

[NeoPa]

It's such a well worded question that I'm sorry I can't be any help :(
I wish all problems were stated as clearly - we'd all have so much more time to help with.
The only things I can say that may be of any use are :
1. Access is famous for reporting completely spurious error messages. Don't rely on the message as reported indicating what the error actually is.
2. To get around the problem you may want to set up a separate database which has just that table in it. This could then be linked to from your front end. You would probably want to execute a 'Compact & Repair on it after using it.

Here is some code to enable the compacting bit :

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "") As Boolean
    Dim strNewDB As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting '" & strDB & "'.")
    If strPW <> "" Then strLocale = ";pwd=" & strPW
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    Call Echo(True, "'" & strDB & "' compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function
 
'Exist returns true if strFile exists.
'22/05/2003 Rewritten with better code.
'20/05/2005 Added finding of R/O, System & Hidden files
Public Function Exist(strFile As String, _
                      Optional intAttrib As Integer = &H7) As Boolean
    Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
End Function

[MMcCarthy]

You say you added more memory. I assume this was RAM as that would have been my first guess. My next guess would be that there is an error in you access installation and you are not using an up-to-date Jet Engine. My suggestion here would be to uninstall your access software and then reinstall it. However, your IT person may be able to upgrade the Jet Engine without doing this.

Mary

BTW, you didn't say what version of Access you were using.

[Rogiecrockett]

You say you added more memory. I assume this was RAM as that would have been my first guess. My next guess would be that there is an error in you access installation and you are not using an up-to-date Jet Engine. My suggestion here would be to uninstall your access software and then reinstall it. However, your IT person may be able to upgrade the Jet Engine without doing this.

Mary

BTW, you didn't say what version of Access you were using.

Mary,

Yes, we added RAM.

Access 2003 Version 11. I copied the details off of the Access 'Help' so everyone could see. Is there something else I am missing that would help?

I will try the uninstall after seeing if we can first upgrade the Jet engine. My guess is they won't know how to do this, nor do I, so we will default to a complete re-install.

Dave.

[Rogiecrockett]

Mary,

I did more checking and found this information under Windows XP in the subcategory 'Jet Core Components'. Can you tell from this if I have the latest Jet Engine? I see on the MS website a 4.0 service pack 8 that has a release date of 15 Oct 2003. My system was set up in Jul 2005. How do I find out if I have the latest?

Thanks,
Dave.


Description Excel
Path C:\WINDOWS\Syst em32\msexcl40.d ll
Version 4.00.8618.0

Description Exchange
Path C:\WINDOWS\Syst em32\msexch40.d ll
Version 4.00.6807.0

Description Jet 2.x
Path C:\WINDOWS\Syst em32\msrd2x40.d ll
Version 4.00.7328.0

Description Jet 3.x
Path C:\WINDOWS\Syst em32\msrd3x40.d ll
Version 4.00.6508.0

Description Jet 4.0
Path Not Available
Version

Description Lotus
Path C:\WINDOWS\Syst em32\msltus40.d ll
Version 4.00.6508.0

Description ODBC
Path Not Available
Version

Description Paradox
Path C:\WINDOWS\Syst em32\mspbde40.d ll
Version 4.00.8015.0

Description SharePoint
Path C:\PROGRA~1\MIC ROS~2\OFFICE11\ MSJSPP40.DLL
Version 11.00.5510.0

Description Text
Path C:\WINDOWS\Syst em32\mstext40.d ll
Version 4.00.8015.0

Description Xbase
Path C:\WINDOWS\Syst em32\msxbde40.d ll
Version 4.00.8025.0

[nico5038]

Having a 1.5 Gb fie is really stressing Access and I would probably try to import the file first into Oracle or MS SQL.
The reason of the exceeded limit might be the fact that your tempfile folder is on a drive not having the needed free space. Normally Access will create a temp database for the session on the C: drive (but your office defaults might differ). When that drive is short, Access will fail....

Nic;o)

[willakawill]

Having a 1.5 Gb fie is really stressing Access and I would probably try to import the file first into Oracle or MS SQL.
The reason of the exceeded limit might be the fact that your tempfile folder is on a drive not having the needed free space. Normally Access will create a temp database for the session on the C: drive (but your office defaults might differ). When that drive is short, Access will fail....

Nic;o)

Are you not approaching the Access size limit here?
chech this out

[Killer42]

Hi.

The Jet version stuff is way over my head, but there are a couple of things I would check.

• Possibility of a corrupted database file. Compact/repair may or may not make a difference there. Also, can you make a copy of the database file, delete the data from it, compact/repair, then import into that one?
• Disk integrity. Have you run scandisk recently? In the past (many years ago, admittedly) I have known a simple disk error such as cross-linked clusters to cause all sorts of weird an apparently unrelated problems.
• Do anything you can to narrow down the problem area. For example:
  • Copying and cleaning out the database as mentioned above.
  • Creating a fresh copy and copying the data into it.
  • Deleting all indexes and trying again.
  • Cool! I didn't know you could do nested lists here until I tried. :)
• This I/T person who can get it to work - is that the SAME database, or a similar one? There's a huge difference. If there is any sort of corruption, it may only be in the one file.
• Have you tried scanning MSDN website or googling the error you obtained, in conjunction with key words such as "MS ACCESS"? Even though this is an MS product, there's still a slight chance that it got the error message right.
• Is there any chance it's something odd in the input text file? Is everyone importing the same file?

[Killer42]

Having a 1.5 Gb fie is really stressing Access and I would probably try ...

Just a minor point - the OP said the database was about 1GB. The 1.5GB mentioned was the PC's estimated RAM.

So unless the text file being imported is really big, there should be no chance of hitting the 2GB limit. As for the rest (temp database, etc) - yeah, definitely worth checking.

[MMcCarthy]

Have a look at this site

http://support.microso ft.com/kb/223320

Mary