Question regarding Access Database size

[kessa]

Hi Guys,

I've got a question regarding Access databases which I hope someone may be able to help me with?

My Access Db is normally around 30mb, but recently we had a client duplicate all of their items in the DB which artificially inflated its size to around 80mb.

I've since removed all of the duplicates, so was expecting it to go back to being around 30mb, but instead it seems to be staying at the size it reached with the duplicates included (at about 80mb)

Any ideas why this may be happening and if there is a way to get the DB back to it's previous size?

Thanks
Kessa

[nathanb]

Download the DB to your computer, use MS Access to compact it, and upload it again.

[kessa]

Hi Nathanb,

Thanks :D - it's gone from 77mb to 22.5 mb :eek: :D

Next question - is it possible to reset / redeclare the following:

a) the unique ID for each record (so if something was ID 1234 and the duplicate was 4567 is there a way to change that back to 1234 (because the original has been deleted)

It would be great if we can do this as loosing the original entries has really messed up the pages which had been crawlled by the search engines - FYI: if something was ID1234 in the database, the indexed page in the Search engines was ref=1234 - because this property is now ID4567 the search engines will regard this as a new page and anyone who has the page saved to their favourites won't be able to access the old record. (and I don't want to have to set up redirects as there's about 5,000 changed records to I'd rather try and fix this in the ID's)

b) declare where the ID should continue on from?
(because the original ID items have been deleted, and currently replaced with the duplicates this means that we have a mass of unused ID's so it jumps from something like ID600 to ID5000 with nothing in between. that in itself isn't a major isue, but it would be nice from a "house cleaning" point of view to try and reduce the gap.

I hope that all makes sense?

Thanks
Kessa

[nathanb]

A) I can't think of an easy way to do that.

B) I think it's possible, but I'm not sure how.

[kruelintent]

Hi,

I have just added a new video to my technical blog showing how to use WMI scripts to programmatically kill remote sessions that are locking an Access database and then compact it.

<a href="http://tech.kruelintent.com">http://tech.kruelintent.com</a>

Hope you find it useful.

Tom.

p.s. I now have an <a href="http://tech.kruelintent.com/blogfeed.aspx">RSS feed</a> from my site too!