By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

MS Access 2003 - System Resources Exceeded Message When Importing Data

P: 3
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
Dec 31 '06 #1
Share this Question
Share on Google+
23 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 'CompactDb compacts a remote Access database.
  2. Public Function CompactDb(ByVal strDB As String, _
  3.                           Optional strPW As String = "") As Boolean
  4.     Dim strNewDB As String, strLocale As String
  5.  
  6.     On Error GoTo ErrorCDB
  7.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  8.     Call Echo(True, "Compacting '" & strDB & "'.")
  9.     If strPW <> "" Then strLocale = ";pwd=" & strPW
  10.     If Exist(strNewDB) Then Kill strNewDB
  11.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  12.                                   DstName:=strNewDB, _
  13.                                   DstLocale:=strLocale, _
  14.                                   SrcLocale:=strLocale)
  15.     Kill strDB
  16.     Name strNewDB As strDB
  17.     Call Echo(True, "'" & strDB & "' compacted.")
  18.     CompactDb = True
  19.     Exit Function
  20.  
  21. ErrorCDB:
  22.     CompactDb = False
  23. End Function
  24.  
  25. 'Exist returns true if strFile exists.
  26. '22/05/2003 Rewritten with better code.
  27. '20/05/2005 Added finding of R/O, System & Hidden files
  28. Public Function Exist(strFile As String, _
  29.                       Optional intAttrib As Integer = &H7) As Boolean
  30.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  31. End Function
Dec 31 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 1 '07 #3

P: 3
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.
Jan 1 '07 #4

P: 3
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\System32\msexcl40.dll
Version 4.00.8618.0

Description Exchange
Path C:\WINDOWS\System32\msexch40.dll
Version 4.00.6807.0

Description Jet 2.x
Path C:\WINDOWS\System32\msrd2x40.dll
Version 4.00.7328.0

Description Jet 3.x
Path C:\WINDOWS\System32\msrd3x40.dll
Version 4.00.6508.0

Description Jet 4.0
Path Not Available
Version

Description Lotus
Path C:\WINDOWS\System32\msltus40.dll
Version 4.00.6508.0

Description ODBC
Path Not Available
Version

Description Paradox
Path C:\WINDOWS\System32\mspbde40.dll
Version 4.00.8015.0

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

Description Text
Path C:\WINDOWS\System32\mstext40.dll
Version 4.00.8015.0

Description Xbase
Path C:\WINDOWS\System32\msxbde40.dll
Version 4.00.8025.0
Jan 1 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 1 '07 #6

100+
P: 1,646
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
Jan 1 '07 #7

Expert 5K+
P: 8,435
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?
Jan 1 '07 #8

Expert 5K+
P: 8,435
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.
Jan 1 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Have a look at this site

http://support.microsoft.com/kb/223320


Mary
Jan 2 '07 #10

P: 3
I hope I am not too late to contribute to this topic.

Yesterday, I ran across the exact same problem for the first time ever. That is despite using text import hundreds of times in Access and having provided a number of clients with utilities for text imports that have worked without fault, sometimes for years.

Environment: MSA 2002 SP3; Windows XP; 1G RAM; plenty of space on drive and in swap file.

Text file source: fixed width; bog standard text – no spurious characters; 407K; nearly 1800 rows; 352 char/row.

Activity: Using MSA Import Wizard to create an import specification. A lot of fiddling about to get the spec right, so lots of “hell with this, let’s give it a try”. All went without issue until teatime (cup of green tea and two organic oat cookies). Then the first sign of trouble manifested: I could not make the Wizard accept an extra break line – I am trying to parse the text into 23 fields. Leaving my attempts to introduce that break line, I run the import and up pops System Resources Exceeded. A couple more attempts and the same thing happens.

Intermission: Saved from irrational behavior by knock at the door. Pretty neighbour calling with an update on divorce and to discuss additions to her Web site. Pleasant couple of hours took me to supper (smoked ham in sauce, mash and two veg, followed by fresh fruit) with no inclination to return to work. So shut down PC.

A Result: After breakfast this morning (shredded wheat with sultanas and milk), started PC, compacted DB (it is very bloated as a result of successive imports yesterday). Started Wizard. No problems: lines went in; imports completed without error. By now, have run import dozens of times without fault.
Also, import works fine under VBA when calling on the spec saved using the Wizard.

Afterthoughts: Select Compact On Close option. I do like selecting this ordinarily. But since this app of mine does no more than import and then export the resulting data in a different format, I have no qualms about setting this option. I shall be interested to see if client’s experience the error when running the app on their PCs (less RAM than mine).

Hope my experience is of use.
Jan 18 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
I hope I am not too late to contribute to this topic.

Hope my experience is of use.
It's never too late to contribute to a topic around here. If nothing else it proves useful for other users searching with similar problems.

Welcome to thescripts.

Mary
Jan 18 '07 #12

P: 3
Mary

Thank you for the welcome.

This is probably not the place to ask this question, but please excuse any impropriety on my part until I find my way round the Network. Is there a way I can mark a topic so that I am e-mailed when it receives any fresh activity?
Jan 18 '07 #13

Expert 5K+
P: 8,435
Is there a way I can mark a topic so that I am e-mailed when it receives any fresh activity?
Just go to your list of "subscribed" threads (on your Control Panel - the link is at top-right) and change the settings for the thread (or change your default options).
Jan 18 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary

Thank you for the welcome.

This is probably not the place to ask this question, but please excuse any impropriety on my part until I find my way round the Network. Is there a way I can mark a topic so that I am e-mailed when it receives any fresh activity?
Follow Killers instructions and if you've any problems let me know.

Mary
Jan 18 '07 #15

NeoPa
Expert Mod 15k+
P: 31,186
Just go to your list of "subscribed" threads (on your Control Panel - the link is at top-right) and change the settings for the thread (or change your default options).
It does have an unfortunate tendency to reset itself however (on a thread by thread basis that is) so you may need to set it again after posting to it again.

This is an issue that is currently Work in Progress so won't be a problem forever.
Jan 18 '07 #16

P: 3
Mary, Killer and NeoPa

Thank you for your guidance
Jan 19 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
No Problem - Pleased to help :)
Jan 19 '07 #18

100+
P: 1,646
Mary, Killer and NeoPa

Thank you for your guidance
Being, mostly, a c++ programmer I much prefer bananas on my shredded wheat with, of course, soy milk (unflavoured) :)
Jan 19 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
I'm sure this is very dry humour but I'm afraid it's gone over my head (loathe as I am to admit it). Can we get an explanation Will?
Jan 19 '07 #20

Expert 5K+
P: 8,435
I'm sure this is very dry humour but I'm afraid it's gone over my head (loathe as I am to admit it). Can we get an explanation Will?
It missed me too, though I BigD did mention having "shredded wheat with sultanas and milk" for breakfast.
Jan 19 '07 #21

100+
P: 1,646
I'm sure this is very dry humour but I'm afraid it's gone over my head (loathe as I am to admit it). Can we get an explanation Will?
Toooooooo funny :D

This really is what I have for breakfast. Just teasing BigD with pretend one-up-manship. You know the kind of thing, "..but 'real' programmers prefer blah blah blah..." while at the same time revelling in the fact that we eat, almost, the same breakfast.

Don't make it a practise to explain my jokes but 2 baffeld pundits are more than a girl can stand :)
Jan 19 '07 #22

NeoPa
Expert Mod 15k+
P: 31,186
Damn!
I always catch up on all the posts in a thread where possible. Clearly in this case I didn't, and completely overlooked BigD's original contribution (At least I remember the first paragraph but nothing about attractive neighbours and food - trust me, I'd remember both of those).
I hate to be in the position normally of asking for explanations (ruins the effect a little) - at least in this case I now understand why. The quote threw me a little too ;)
Jan 19 '07 #23

Denburt
Expert 100+
P: 1,356
Just an FYI for anyone that receives this message I incurred this error message a few minutes ago and managed to resolve it. The database that I am working on is very complex as far as the forms, queries, reports, etc. but over all it isn't very big, the front end may jump to 100 MB at times but when compacted is only about 25 MB I have had many many issues with this DB corrupting forms, reports etc. so that may account for some of the strange behaviors such as the one I just encountered.

The error occurred after I created the MDE file. The MDE file worked but the MDB gave me the error mentioned in this post as soon as it was opened. Believe it or not I did a compact/Repair to no avail so I did a complete reboot of the P.C. Opened the database and received the error after another compact/repair I went to look at the tables and it didn't show any tables or queries... Just for Kicks I created a new table with 1 field and saved it... Seems that after I did that the tables and queries showed up the error went away and I can resume my tasks even after I deleted the dummy table I just created. Very odd indeed but sometimes you never know what may or may not fix a problem.
Oct 1 '09 #24

Post your reply

Sign in to post your reply or Sign up for a free account.