Problem with Compact/Repair

Hi All,

Using WinXP pro/Access 2000.

I have a database that's been used for about 5 months. Transferred lots of
data from a dos based program, then the users have been using it for that 5
months. About 180000 records in one of the main files (maybe 25000 new since
the dos conversion). Been having some speed issues crop up along the way
(much faster to do same reports when I first transferred the data than now,
with the new 25000 records). So, thought I'd run Compact/Repair.

Of course I did the compact/repair on a copy of the mdb. After the c/r,
while still on the same computer, the reports that had slowed down over this
time, speeded up again. Yippee! So, I saved the old file as a backup, and
made this recently c/r'd file the one to link to for all the users (4 others
plus the one where I did the c/r, so 5 total). Only problem is, nobody else
can get at the files. Everyone else's front end says the back end is
unavailable/locked by another user when they try to link into it. Even
trying to go in and just open the backend file can't be done (so,
eliminating the front end). The computer where I did the c/r is fine--has
access to all the data, no problems. Any other user is locked out. I put
the old, un compact/repair'd file back in, and all the users can link in
fine. But, it's slow again.

I've tried compacting/repairing on a different machine and I get exactly the
same results--that user can use it, but nobody else can. I've looked thru
all the files (pre and post c/r) and nothing *looks* odd with the
structures, relationships, etc, so...

Ever heard of this happening?

Does anyone know what the heck is going on and how I might approach making
things right so that compact/repair doesn't break everything?

TIA
ron

re: Problem with Compact/Repair

I have to assue that all users are not in this database.

Furhter, I assuem you are running a split database..right?

To compact and repair, you simply have to ask all users to exit thedatabase,
and then open up the back end file..and then do a copact and repair. At this
point, users should be able to launch their front ends and continue to
work....

It sounds like some file is locked, and I owuld consider re-booting the
machine the back end is on...

After you compact, there should be no need for the front ends on each pc to
re-link unless you changed the file name...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

re: Problem with Compact/Repair

You assumed correct. No other users in database while this
compacting/repairing was done.

Yes, again...split database with 5 users having the front end, and the main
user (one of the 5)has both front and back but still split into 2 separate
databases (and folders) and linked to the front end.

Your 3rd paragraph is still the problem. The main user who has both front
end and back end (which, works out to also be the machine I do the compact
and repair on), can use the files after the compact/repair. The other users
get an error message when trying to use the files. Error is "The Microsoft
Jet database engine cannot open the file "blah blah.mdb. It is already
opened exclusively by another user, or you need permission to view it's
data."

Now, I should probably mention here that there are 3 separate databases the
users can go into at startup and these databases are shown on a list, and
it's only this recently compacted/repaired database they can't get into.
The other 2 are fine. And if I put the old file, the one before
compacting/repairing back where the other 2 are...so, no compact/repair
having been done...then all users can open all 3 files--no error like above.
It's only after I compact/repair this database that I get this problem, and
only with this file.

I tried your suggestion of shutting the computer off although I was pretty
sure it wouldn't make a difference since I had tried the same compact/repair
thing on a different computer in the network--I had already moved the
database in question to a different computer, ran the compact/repair and had
the same problem on that machine. But, I did try to shut all the machines
off, and start over again. Same problem.

I even took the datafile in question to another office network and everyone
on the network could access the database, as normal, UNTIL I
compacted/repaired it on one of the machines. From then on, no one else
could get access to the files except the machine where I did the
compact/repair.

Seems to be JUST this database. So, tried to compact/repair on the other 2
databases...they're fine. Everyone has access afterwards. It's just this
one database.

Again, anyone know why this would be happening..and how I can fix it?

Thanks
ron


"Albert D. Kallal" <PleaseNOOOsPAMmkallal@msn.comwrote in message
news:1orTh.65557$aG1.62566@pd7urf3no...