Error on delete

[Basil]

Hiya,

I import data to a new file. I then run an append query to
fiddle with the data and shove it into an existing table.

Before I do the append query I delete the table's
contents. There are over 30,000 records and a lot of
indexes.

Anyway, on my delete query it takes ages and ages and
keeps giving me an error: Cannot delete a record because
of file sharing blah blah blah - there is nothing using
this table though!!

I have also just fiddled a bit whilst writing this and
realised that in the process the database has gone up from
15MB to 199MB and won't let me back in. Damn.

I have got months of work in this bad boy (back up is a
few weeks old). Please help:

1. Why did it come up with an error when trying to delete
the table's contents?

2. Why did it huge up in size, and how can I stop this
from happenning?

3. Why won't it let me back in coz of a "sharing
violation" or something when nobody else has it open?

Sorry to trouble your xmas with this. Thanks for any help.

Basil
PS. It just let me back in weirdly and I managed to
compact it. But I cannot afford for this to happen when
it goes live - a hospital will depend on it.

 

[Wayne Morgan]

Answers in line below.

--
Wayne Morgan
Microsoft Access MVP

1. Why did it come up with an error when trying to delete
the table's contents?

I don't know, you haven't shown us what you did to do the delete.

2. Why did it huge up in size, and how can I stop this
from happenning?

See #1. I'm guessing that Access was using a "built-in" temporary table
during the delete and it didn't get cleared when the error occurred.

3. Why won't it let me back in coz of a "sharing
violation" or something when nobody else has it open?

Close the database and see if the is a lock file (*.ldb) in the same folder
as the database. Check the folder of the front end, back end, and system
(mdw) files. If there is an ldb file and all databases are closed, delete
the ldb file(s). If any databases are open, don't delete the ldb file with
the same name as the open database files.

Make a copy of your database file for safety then try to open and compact
the database.

 

[John Spencer (MVP)]

Basil,

I would advise you to take a good long look at using Access and Jet for your
data needs if a hospital will depend on it. Access plus Jet is a good product,
but it may not be robust enough for a critical hospital system. You may need to
look at using a more robust backend such as MS SQL or Oracle or another product
that has stronger security, better recovery procedures, and more capability to
handle data errors.

Using Access may be a valid choice, but it is possible that using Access is not
be a good choice. Hard to tell without complete knowledge of the requirements.