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
1  5122 
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********@gma il.com> wrote in message
news:11******** **************@ p10g2000cwp.goo glegroups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Roger Redford |
last post by:
Dear Experts,
I'm attempting to marry a system to an Oracle 817 datbase.
Oracle is my specialty, the back end mainly, so I don't
know much about java or javascript.
The system uses javascript to make ODBC calls to the db.
The particular system I'm working with, will not work
with an Oracle stored procedure I'm told. However, it
|
by: Jacob Pallapati |
last post by:
Hi,
I need to create an ODBC connection for one of my databases from the
command line. I could use db2 catalog system odbc database source
<my-database>. But I want to create a DSN with a name different from
the name of the database. For instance if my database name is SAMPLE i
want to create a dsn with name sample_database. Please let me know if
there is a way to do this from the command line. As far as I can see
db2 catalog command...
|
by: Lauren Quantrell |
last post by:
Maybe a dumb question - I'm new to ODBC. How do I install an Access
..mde file on a user's workstation and create the ODBC connection to
the backend SQL Server database without having to go through the
Administrative Tools>Data SOurces (ODBC) menu? IS there a way to do
this with VBA code from my installer disk, or by simply copying a file
onto the user's PC?
Any help is appreciated.
LQ
|
by: Alec |
last post by:
Hi All,
I am currently trying to link in Access 97 to a table in a MSSQL 7
server. Initially the link is fine, however, when I close the access
database and re-open it from the same network lacation on a different
machine, it tells me that the ODBC connection to 'SQL Server Name'
failed.
How can I create the linked table permenant no matter what machine the
networked database is opened on?
|
by: Travis |
last post by:
I am trying to display a query like this in my windows form:
SELECT workordernumber, date, firstname, lastname FROM customer_db;
I am using a rich text box to display the data but it only displays ONE
record in the rich text box. How do you display all the found records.
I am using the System.Data.Odbc;
it is quite easy to use, but I cannot figure out how to display large
| | |
by: juststarter |
last post by:
Hello all,
I am executing a stored procedure (on an SQL Server) using ODBC but i can't
get the output parameter's value on the client. The stored proc has 3
parameters ,2 of them are input and 1 is output. (for shake of simplicity
let's suppose that the proc seems something like
--------------------------------------------------------------------
alter storedProcsName @inputParam1 varchar(20), @inputParam2 varchar(20),
@outputParam...
|
by: jwtulp |
last post by:
Hello all,
I receive the following error when updating MSAccess2003 Memo fields
using an ODBC connection in ASP.Net 1.1 when the length of the text to
be updated exceeds 255 characters. Everything works fine when the
length of the text stays below 255 characters. Does anyone have a
clue?:
ERROR Invalid
precision value
|
by: datapro01 |
last post by:
Running DB28.2 on AIX 5.2.
I have two questions if its possible for anyone to assist.
1. Is it possible to make use of a 'Shadow Catalog' on DB2 UDB v8.2 on
AIX? If so, how? Can anyone give me an example of the SQL to create
the appropriate view and catalog table?
2. Is there anyway via the ODBC datasource parameters to influence the
SQLTables and SQLColumns stored procedures to use a schema_name when
|
by: Santosh |
last post by:
Hii all i have written following code for inserting records into
database but it gives me an error espected parameter @NewsTitle which
is not supplied.
OdbcConnection conn = new OdbcConnection();
protected System.Web.UI.WebControls.Button Button1;
OdbcCommand cmd =new OdbcCommand ();
protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.TextBox TextBox2;
OdbcParameter param =new OdbcParameter();
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
