chaduka
asked on
Database Compacting
Is there a way of compacting a MS Access database (or any other db) using SQL?
You mean "making compact", like zipping?
ASKER
No. When you delete stuff from the database, your file size does not change unless you compact it. This improves database searching.
Errrm another question, is there a way of (after deleting records with an autonumber field) haveing say:
|-----------------
|EntryID | Entry |
|-----------------
|1 | blah1 |
|-----------------
|2 | blah2 |
|-----------------
|3 | blah3 |
|-----------------
After deleting record 2, is there a way of having the following setup?
|-----------------
|EntryID | Entry |
|-----------------
|1 | blah1 |
|-----------------
|2 | blah3 |
|-----------------
...that is, I have reordered my Autonumber field with successive numbers.
Errrm another question, is there a way of (after deleting records with an autonumber field) haveing say:
|-----------------
|EntryID | Entry |
|-----------------
|1 | blah1 |
|-----------------
|2 | blah2 |
|-----------------
|3 | blah3 |
|-----------------
After deleting record 2, is there a way of having the following setup?
|-----------------
|EntryID | Entry |
|-----------------
|1 | blah1 |
|-----------------
|2 | blah3 |
|-----------------
...that is, I have reordered my Autonumber field with successive numbers.
ASKER
Adjusted points to 50
Sorry, never done that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is a CompactDatabase method in access, but my understanding is that once you use an autonumber, it's verrry difficult to remove/renumber it the way you want to. Access help had this to say about the CompactDatabase method:
If you delete records from the end of a table that has an AutoNumber field, when you compact the database, Microsoft Access will reset the AutoNumber value for the next added record to a value of one more than the last undeleted AutoNumber value.
So it only does part of what you want it to do. However, your question asked if this is possible using SQL. I can't think of any way that would be possible. SQL is such a general language that it won't support specialized functions pertaining to any particular database. I suspect that you would have to use something like VB, VBA or VBScript.
If you delete records from the end of a table that has an AutoNumber field, when you compact the database, Microsoft Access will reset the AutoNumber value for the next added record to a value of one more than the last undeleted AutoNumber value.
So it only does part of what you want it to do. However, your question asked if this is possible using SQL. I can't think of any way that would be possible. SQL is such a general language that it won't support specialized functions pertaining to any particular database. I suspect that you would have to use something like VB, VBA or VBScript.
Oh, I almost forgot. You should really post this question in the Database:Access area instead of here. There are some very very knowledgeable people there who may be able to help you, or at least set the record straight.
Good Luck
Good Luck