ODBC parameters and system resources

[mattias192]

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

[Jerry Boone]

Just a suggestion - not a fix, although I suppose one could consider it a
fix if it took less time and worked better for you...

Install MSDE or use an existing SQL Server. Upsize, DTS, or BCP the tables
from the mdb onto the MSDE or SQL server. Then link the SQL tables into
your MDB and all of those messages will go away. Of course, you will likely
be presented with some new error messages relating to datatypes in the
tables but those are fairly easy to resolve.

--
Jerry Boone

"mattias192" <ma********@gmail.com> wrote in message
news:11**********************@p10g2000cwp.googlegr oups.com...

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