PROBABLY Very Simple???

[abesimpson]

2 Questions

1 I am working with a HUGE database, so very thing that I am doing takes considerable time to execute. Weekly I exprort the results of my database to a CSV file. I do this by running the Wizard and following instructions. Each step of the Wizard operation takes about 45 minutes to execute before instructiing me to click next, finish, etc. I am not compaining about the time Access needs to process the info, just wondering if there is a better way to export the result of a query to a CSV file, something that I can execute and walk away from.

2 Weekly I import new data to the database. To strip the old data I the empty database under a new name (retaining the database structure), delete the old database, then rename the empty database to the "OLD" database name. Is there an easier way to strip the data from the database?

As always, thanks in advance.

abe

 

[Mudface]

How many records do you have? It sounds like you have a very decent case to upsize to SQL Server or Oracle.

 

[abesimpson]

The database is about 1.5gig.

This is comprised of a couple of tables about 1meg each and several somwhat smaller tables manipulated through various queries.

From your question, I can guess that I am pressing the limits of Acess, but I am not able to invest significant $'s in software so am not sure if there will be s solution for me.

Thanks for the speedy reply.

 

[ChrisUK]

How much NEW data do you import each week ?

When you delete the OLD data Access does not delete it it just flags the data for deletion, you need to compact the database as well or it just continues to grow.

what's your PC spec ? maybe you need to upgrade this, more memory ? Upsizing to SQL Server may be using a sledge hammer to crack a nut, but then again it might be what you need to do!

My advice would be to try the deletion of old data and then compact the DB.

HTH

 

[SydneyGeek]

If your db is over 1000 times the size of your largest table, I think you need to compact it. Go to Tools | Database Utilites | Compact Database (Access 97) or Compact and Repair Database (higer versions).
This could take a while to run but you will end up with: (1) a much smaller DB (2) quicker performance and (3) less chance of corruption

Denis

 

[abesimpson]

I'll try compacting and report my results back.

Any suggestions for a simple way to empty the tables?

thanks

abe

 

[SydneyGeek]

If you need to get rid of all data from a table, try this:

New query, design view
Pick your table from the list. OK.
In the grid, double-click the asterisk to select all fields.
Run the query -- you should see all records and fields.
Switch to SQL View.
Change SELECT to DELETE
Save the query as qryDELETE_MyTable (change MyTable to suit)
Close
To clear the data, double-click the query icon. You will be prompted about deleting X number of records. OK, and they are history.

Denis

 

[ChrisUK]

and if you don't want to be asked "Are you Sure" each time there is a global setting you can change .... just can't rememmber where it is!! sorry

 

[mdmilner]

An 'empty' database should be tiny - varies by the code/queries setup, but if it's over 1 MB, compact it. Pay attention to the before/after file size.

Access is a packrat for file size. Think of it as an accountant that does everything you ask but makes extensive notes on everything you do - that had to buy a half dozen file cabinets to help organize a sorting procedure (to run a query) but those cabinets are still sitting around even and cluttering the office (workspace) although the query was deleted a long time ago. Deleting objects does not delete the space they required to store. That's what compacting does.

Based on re-reading your message: Compacting has to be the problem. You're copying the full database to a new name, deleting the information and saving it, then renaming the now "empty" database to the live name.

Your current copy of access is 'cluttered' by everything that has been done in it since the day you created it (or last compacted it).

Mike