File size grows beyond capacity of Access with append query.

[DISI]

I have a 230 meg txt file that I append into an empty Access table (Access 2000). The text file contains data from an ad hoc (MUMPS) off of a data warehouse. The access table has 16 fields, 2 of which are indexed. These two indexed fields plus a third make up the primary key. The append query does no formatting.

I consistently have the following problem (I archive this type of data monthly): I will need to run the append query 6 or more time before it works. The database usually grows to 2+ gigs and the query crashes. I start fresh with a new database and try again, until it finally works. The database grows to 664 megs and compacts to 533 megs.

Why is it that when I append 6 times out of 7 (or so) it grows to 2+ gigs and crashes, while if I keep trying (rebooting to clear memory in between) it will eventually work. I am running a Dell 256 megs of RAM and an Intel PIII with 3.5 gigs of hard drive available.

Any ideas???? Thanks - Paul
Paul Faculjak

Paul Faculjak
paul@DataIntegritySolutions.com

 

[JoeMiller]

What version of Access are you running? If it's 2000 you may want to look into making sure that you have all the service packs installed. I've heard 2000 had a major problem with "file bloat."

HTH Joe Miller
joe.miller@flotech.net

 

[JoaoTL]

Hi, in the beginning i had the same problem with DB growing. I get a lesson and put a reminder in my Monitor that says *CLOSE ALWAYS YOUR VARIABLES AND RECORDSETS, EMPTY ALL VARIABLES THAT YOU WILL NOT NEED*.

e.g.

rs.Close
Set db = Nothing
Set rs = Nothing

Usually this problem can be solved by this example, let me know.

Hope it helps -------------------------
Regards
JoaoTL
-------------------------

 

[DISI]

I see how closing variables, record sets and emptying variables may help. However, I am running NO CODE and am starting with a fresh (empty) database (link to the txt file, 1 empty formatted table and 1 query). I am wondering if anything else may be going on here.

Paul Paul Faculjak
paul@DataIntegritySolutions.com

 

[tlbroadbent]

Are the indexes and primary key defined at the time you attempt to insert the data into the table? I could see where you might define a primary key to prevent duplication. I suggest removing the indexes until the data is loaded. If you don't need Access to check for duplicates, remove the primary key also. You can add indexes and keys after the data is loaded.

If you leave the key on the table, make sure the insert query sorts the data in primary key order before inserting.

You may want to break the insert into multiple inserts. If there is a logical way break the data into sets, say by date, then parameterize the query or create multiple queries. Insert a range of rows, compact the database, insert another range, etc. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[DISI]

Thanks for the ideas. However, I believe I stumbled upon the answer this morning. I usually burn a CD with the txt file and then transfer it to my PC. The txt file property is "read only" coming off the CD. When I remove this property the append works fine. The same was true then I tried appending a very large MS Access table when the source database property was set to "archive". Removing archive allowed the append without "invalid argument" error and a huge file.

Make sense to anyone?

Paul Paul Faculjak
paul@DataIntegritySolutions.com