Re: Rapidly expanding MS Access data file size
- From: "Dale Fye" <dale.fye@xxxxxxxxxx>
- Date: Thu, 6 Dec 2007 22:16:27 -0500
Guys,
The problem is that you are appending to a temp table in your local
database, and then deleting the data from that table.
My recommendation is that in addition to the SQL server database, you create
a separate Access MDB file for your temp tables, and link those tables to
the application that is managing the imports. If you have multiple front
ends, and each person is doing their own imports of data, then I would put
that Tempmdb file on their local machine. Otherwise, you could put it on
the network if you only have one person or an automated process doing the
imports. Then, when Access imports the new file, it is actually importing
into a table in Temp.mdb.
When you finish the import, and delete the data from the temp table, you
could use the CompactDatabase method to compact the temp.mdb file. You may
have to delete the link to Temp.mdb before you do the compact operation, but
I don't think so. Just make sure that you don't have any forms or
recordsets that open that are connected to that mdb file. Additionally,
since the CompactDatabase method requires that you provide a new datbase
name (I use Temp1.mdb), you will have to check to see if this file already
exists. If so, you will need to delete it before compacting. Once the
CompactDatabase method is complete, you will have to delete the old file
(Temp.mdb) and Rename the compacted version (Temp1.mdb) to Temp.mdb. Once
that is complete, you will need to refresh the link for the table that is
linked from that database.
An alternative to this is to have a copy (empty) of the temporary table in
your application database. Then, every time you need to import a file, you
could use the CreateDatabase method to create a new database (Temp.mdb)
somewhere on the users hard drive. Then, use the TransferDatbase method to
export the empty compy of the table to that database. Then, you create a
link to that table, again using the TransferDatabase method (this time with
the acLink parameter). When you are done importing that file, you can
simply delete Temp.mdb, because you will be creating it again when it is
needed.
A last consideration is that you don't really need to compact the temporary
datbase each time it is used, you could use the FileSystem object to
determine the size of the mdb and when it reaches some limit that you have
established, then do the compact or delete/create database.
HTH
Dale
"Hans Mol" <HansMol@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:60220FF0-3019-47FF-A54F-40D351394EDF@xxxxxxxxxxxxxxxx
I have the same problem which needs to be solved as approx 25 people use
the
database and some odbc connections are made, therefore compaction is very
hard (database must not be in use)
I found 2 tables to be the reason for increasing the size (200Mb to 1000Mb
in one week)
the database is a backend so only tables are in this file nothing else.
the number of additions to the tables in not much (approx 30/day) and the
number of edditions is approx 100/day.
the tables have about 150000 records each and there are a lot of empty
fields.
the increase only started a couple of weeks ago afte 1,2 years of use
"Informate-Rod" wrote:
I have a small Access 2000 app that whilst working is causing some
concern.
The app is a VB module that is constantly running. The module imports
lots
(30000+/day) of simple CSV style files, applies business rules to the raw
imported data and updates tables in an attached SQLServer 2000 database
under
transactional control. The raw imported data is deleted before new data
is
imported. An ODBC connection object is established at App startup and the
same connection is used to perfom all accesses to the SQLSrvr repository.
The
app should run 24*7. The app is activated by calling it from an
'AutoExec'
macro that automatically runs on startup.
The app runs fine except that the size of the access file grows rather
alarmingly quickly. i.e. 3->14Mbyte in 2 hours. If you terminate and
merely
'Compact & repair the database' the size goes back to the original 3 Mb.
I've doublechecked the code looking for obvious problems - making sure
all
recordsets get closed etc - everything looks fine.
VB is not my normal language so I'm a little wary that I've ballsed-up;
looks and feels like a classic memory leak but I do not know memory
alloccation rules in VB.
The only other thing that might be a problem is if there are issues with
FileImporting using FileImportSpecs.
Anyone got any bright ideas??
cheers
Rod.
.
- Follow-Ups:
- Re: Rapidly expanding MS Access data file size
- From: Hans Mol
- Re: Rapidly expanding MS Access data file size
- Prev by Date: Re: adding text to time
- Next by Date: Re: Import from excel to access is dropping rows?
- Previous by thread: adding text to time
- Next by thread: Re: Rapidly expanding MS Access data file size
- Index(es):
Relevant Pages
|
|
