ODBC parameters and System Resources

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I cannot make sense of the ODBC error messages my VBA application
throws at me. I connect to an Access database of about 500MB in size.

First, there is the "Not enough space on temporary disk". This happens
when I insert many rows to my Access database. If I increase the ODBC
Buffer size from 2048kB to 8192kB the inserts seem to work fine.

When I try deleting the rows (still from my VBA application) it
complains about that MaxLocksPerFile is set too low. Fair enough, I
increase it to 30000 (arbitrarily chosen value) from the default value
9500 and it works.

But, now when I try to insert rows again, I get an error message saying
"System resource exceeded". Not very helpful I must say.

I have experimented with different values of these parameters back and
forth but I just can not make sense of it. There seems to be no easy
relation between them and the resources taken up by the application.

As mentioned, my database is ~500 MB and the number of rows
inserted/deleted is about 750000 where each row contains let's say 6
Number fields. I run Access 2003 on a Windows 2003 Server SP1.


These are (some) questions that spring to my mind...

A) What is the "temporary disk"? My environment variable TEMP points to
a disk where there's lots of free space (~10GB). The same goes for my
working directory in Access.

B) Is there any way of estimating a suitable value for the
MaxLocksPerFile parameter given the number of rows, the size of an
average row and the size of the RAM?

C) What can a "System resource" be apart from disk space and RAM? The
total number of File Locks?

D) Can I redisign the deletion/insertion of rows so that Access/Jet
does not consume that many "resources"? As of now the whole
deletion/insertion is done in one SQL statement.

Suggestions, anyone?

Mattias

.

Relevant Pages

  • Re: ODBC parameters and system resources
    ... Install MSDE or use an existing SQL Server. ... I connect to an Access database of about 500MB in size. ... there is the "Not enough space on temporary disk". ... What can a "System resource" be apart from disk space and RAM? ...
    (comp.databases.ms-access)
  • ODBC parameters and system resources
    ... I cannot make sense of the ODBC error messages my VBA application ... I connect to an Access database of about 500MB in size. ... there is the "Not enough space on temporary disk". ... What can a "System resource" be apart from disk space and RAM? ...
    (comp.databases.ms-access)
  • Re: Form design
    ... >> I have been asked to do a quick access database to schedule use of ... If you can invest the time and practice, ... > Access/VB programming project." ... > learn to code VBA modules. ...
    (microsoft.public.access.forms)
  • Re: Access 2007 and Visual Studio 2008
    ... you must use VBA in an Access database. ... My App will be on a remote file server and users will operate using links ... To build a pure Access 2007 application it seems that i'm stucked with VBA ... as visual studio 2008 does not support Access in new VSTO tools for Office ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBScript vs VBA for Outlook Project
    ... projects so I'm familiar with VBA. ... From what I've read, VBScript can't do that, but there may be an alternate ... ' Connection string for MS Access database. ... ' Drop table Customers if it exists. ...
    (microsoft.public.scripting.vbscript)