473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Deleting lots of records and record size

I'm working on an application that imports and exports tons of CSV data,
like 64,000 records per file, and six or seven files in a set.

First off, by the tine I import a few hundred thousand records the database
is _huge_. Like 500 meg, although the csv files are only 25 meg in total.
When I compact the database its still 100meg. How do I keep the size down?

Second, I did a manual delete of 300,000 records yesterday by doing a
select-all in the table and then delete. Is there a faster way? Granted my
computer is no speed demon, but it must have taken twenty minutes or more.

Third, during that twenty minutes, Access was displaying that lovely little
"not responding" message in the title bar. Any way to get rid of that
message?

Thanks,

Mike
Nov 13 '05 #1
3 1972
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote:
Second, I did a manual delete of 300,000 records yesterday by doing a
select-all in the table and then delete. Is there a faster way? Granted
my computer is no speed demon, but it must have taken twenty minutes or
more.


I think a delete query might speed up performance.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #2

"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote
First off, by the tine I import a few hundred thousand records the database is _huge_. Like 500 meg, although the csv files are only 25 meg in total.
When I compact the database its still 100meg. How do I keep the size down?

Are you importing to a new or existing table? If new, Access defaults the
field types to text usually, and the field size to 255. Import to an
existing table.

Write code using AddNew to import. This is *may* be quicker, and allows you
to put some DoEvents in the Loop, so that your db will not be "locked up"
Darryl Kerkeslager


Nov 13 '05 #3
On Tue, 11 Jan 2005 08:41:32 -0800, "Mike Turco"
<mi*******@yahoo-nospam4me.com> wrote:
I'm working on an application that imports and exports tons of CSV data,
like 64,000 records per file, and six or seven files in a set.

First off, by the tine I import a few hundred thousand records the database
is _huge_. Like 500 meg, although the csv files are only 25 meg in total.
When I compact the database its still 100meg. How do I keep the size down?

Second, I did a manual delete of 300,000 records yesterday by doing a
select-all in the table and then delete. Is there a faster way? Granted my
computer is no speed demon, but it must have taken twenty minutes or more.

Third, during that twenty minutes, Access was displaying that lovely little
"not responding" message in the title bar. Any way to get rid of that
message?

Thanks,

Mike

Deleting a lot of records which are indexed takes a long time
particularly on back ends. Depending on the database you are using you
can kill the table and replace with an empty copy, use delete object
whatever, or delete the indexes before deleting the records and then
recreate them. The latter may be needed if you can't kill the table
(eg if there are relationships).
David
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rich | last post by:
I have a page that list all records in an access table. I would like to add a delete record function to the list to delete selected records. Here is my current code: ...
1
by: Mark | last post by:
This question refers to a main form with a continuous form subform. After an error occurs after entering several records in the subform, how can I delete all the data in the main form and all the...
3
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
2
by: Emmett Power | last post by:
Hi, I have an Access table with a number of records which refer to the same person but with data in different fields. So for example the table would look like this: Name..............Field...
2
by: uv | last post by:
Hi! I'm having problems submitting a new record through the form. I'm working with the wizard and I've added a control button to my form for entering entering a new record but for some reason it...
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
2
by: Stuart Norris | last post by:
Dear Readers, I am developing an application that stores "messages" in array list and writes new entries to a disk file at the end. This file is used incase the user choses to restart the...
1
by: Shortstuff12345 | last post by:
This can be ignored... I did a bunch more searching and tried a few more things listed on different posts. I was able to get the delete query to work properly by adding a statement in the code...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.