Database Compacting

Is there a way of compacting a MS Access database (or any other db) using SQL?
LVL 1
chadukaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

punkerCommented:
You mean "making compact", like zipping?
0
chadukaAuthor Commented:
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.
0
chadukaAuthor Commented:
Adjusted points to 50
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

punkerCommented:
Sorry, never done that.
0
punkerCommented:
I can tell you, though, that renumbering a key ID field in Access is not possible. It's difficult to do it by "tricking" the database by hand, no less doing it automatically through CF.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wEEpyCommented:
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.
0
wEEpyCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.