jfromanski
asked on
The need to compact Access database
I have simple JET database in my application: one table, 9 text columns with limited width, indexed by first column [ID] - see code. This table is accessed using connection string like "'Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=MyData.mdb;Persist Security Info=False" (more specifically I'm using Delphi 6 and TADOTable/TADOQuery). From time to time the content is to be replaced by external data (approximatively 13000 records in .csv file, 500 kb size). The way I'm doing it:
for every record read from .csv
try
INSERT // this produces an error if ID exists
except
UPDATE
end
And now the problem.
My database is about 1Mb. After content replacing it grows to 250Mb. When I compact it then it is back to 1MB. I can use algorithm like
if record exists then UPDATE else INSERT
and then database size is stable, but it is a 100 times slower.
Is it normal behaviour or I'm doing something wrong?. The program is designed to be run on various machines and sometimes this extra 250Mb can be a problem.
The enviroment is Delphi 6, Win XP SP3, Access 2010 installed.
for every record read from .csv
try
INSERT // this produces an error if ID exists
except
UPDATE
end
And now the problem.
My database is about 1Mb. After content replacing it grows to 250Mb. When I compact it then it is back to 1MB. I can use algorithm like
if record exists then UPDATE else INSERT
and then database size is stable, but it is a 100 times slower.
Is it normal behaviour or I'm doing something wrong?. The program is designed to be run on various machines and sometimes this extra 250Mb can be a problem.
The enviroment is Delphi 6, Win XP SP3, Access 2010 installed.
CREATE TABLE [players] (
[ID] TEXT(5) NOT NULL DEFAULT '00000',
[IMIE] TEXT(20) NOT NULL,
[NAZWISKO] TEXT(30) NOT NULL,
[WK] TEXT(4),
[OKREG] TEXT(3),
[INFO] TEXT(2),
[KLUB] TEXT(50),
[ZNIZKA] TEXT(1),
[SKLADKA] TEXT(1),
PRIMARY KEY ([ID])
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER