No read permission when compacting 2

[KenG]

When I try to compact the backend of my database I get the following error message
"Records can't be read; no read premission on \\..."
There is no security on this db, when I use it I am recognized as the Admin, and I went through all the objects and set all of the permissions so that I can do anything (read/write/administer), and I've 'repaired' the file.
The database works otherwise. But I know that I need to compact this thing!
Any idea about what's going on?

 

[Terpsfan]

Does the back end of your database reside on a network share? I've gotten this message when there were some network problems with NT in which I was losing permissions temporarily in the directory the database was on. Does this happen all the time or some of the time? Make a record of when it occurs. Otherwise you may have something corrupting data in your database.

 

[KenG]

Thanks for the reply.
I checked the directory where the backend is and found additional .mdb files, db1.mdb db2.mdb, etc.. I guess these are the temporary files that Access makes when it compacts the db. Each one of these is a copy of the original db - just compacted! I changed the name of one of these to the name of my backend db and it seems like it works.
Does anyone see anything wrong with this plan?

 

[JimRich]

May I add a variation to this thread? I have the same problem on a database I copied from my clients NT network (MS Back Office 2000). When I try to compact it on my office computer I get the same message as above. My office computer is standalone (no network). Same versions of Access in both locations.

I got the same message when I tried compacting at my clients location. All users on all computers have total permission and rights. There are no offending *.ldb files.

If I need to start a new thread, let me know and I will. Thanks.
Jim

 

[KenG]

I'd like to know if anyone else has the same problems as JimRich and I, and what (if anything) they've done to resolve it. I'd also like to know if this is a common 'feature' of MS Access.

btw, earlier today I lost all of the various ODBC drivers (I mainly use Excel and text) on my machine - nothing new here, third time this has happened. But this time I can't reinstall any of them. The system is convinced that they're already installed - therefore I can't install them!!

I know this isn't really related to Access and this thread, but I needed to vent a little. %-(

 

[Quehay]

This is more observation than helpful insight, but the db1, etc. files are the new mdb file created in the compact process. The existing file should be deleted and the db1 renamed as original in the complete process--sounds like the write privileges on the folder/file get garbled and it allows you to create a new "db1" but not delete the old file. I sometimes get the occasional "db1" file showing up in the folders and have been attributing it (without certainty) to network timing errors in heavy usage periods.

 

[KenG]

I'm sorry Quehay, but that was actually a helpful insight. ;-)
In the permissions for the directory in question I added myself, instead of me in a workgroup, and now I can compact the db without the error message. I know this isn't a real fix, so lets call it a short term work-around.
Thanks for the idea,

 

[JimRich]

Thanks for the help and for letting me join in. I have one other question about the "db1" file. How can I be sure that all the tables in the database were compacted and that no individual records are left out.

This may not be an issue at all but I have to be extremely careful with my clients data, as I am sure you are also.

Thanks!
Jim Richardson

 

[IndyGill]

Hi People, I am having a similar problem

I am currently building an Access 2000 Database which analysis’s commercial BT Phone Bills, as you can imagine these bills contain over 100,000 rows of data. The Database is becoming very large, 660 Meg to be exact and growing, I tried to compact and repair the database to reduce the size.

However Access seems to lock up, all I am left with at the end is a Db1 file. What is this file, is it only created when an error has occurred or Access has locked up. I’m not sure if any information is lost. Am I hitting the limits of Access by creating a Database so big?

Any advice on this topic would be very welcome

Thanks in advance

 

[Quehay]

IndyGill: (read a couple submissions above to get the db1 info) You need to get that data the hell out of Access and into a DBMS up to the job (SQL Server / Oracle). I'd put that info into CSV flat files ASAP as an Access-independent backup.

 

[TheInsider]

I currently have this very problem. I have a database which when the tables are empty, it compacts without any problems. This database is a new version (complete re-write) of an old inefficient database. I have written a program (being carefull not to violate any integrity) to import all of the records from the old database and place them in their respective tables in the new database. After the import the database gives the same "Records can't be read; no read premission" error message during a compact. I can repair the database without any problems but it doesn't fix this.

This database is on my local computer with default Admin permissions. Quehay is probably correct in saying that the new db1.mdb should be used. I notice that the db1.mdb compacts without problems.

However, I have 2 concerns... 1) The possibility that db1.mdb may have corruption, and 2) I will be distributing the new database and import program to 50 remote inexperienced database users. They will be performing the import process and I really don't want to complicate matters. If anyone knows of a cause or solution to this problem, please let me know.
Thanks, Rob Marriott
rob@career-connections.net

 

[Quehay]

Rob:

I can't vouch for db1--I don't know what state it's left in if the compact isn't finished. I'd guess that everything is done except the Kill for the old file before renaming db1. I wouldn't be your job on it though...maybe some of the bottom feeders will come up for this one.

For another stab in the dark I'd say try running the security wizard and overtly set the needed permissions for Admin user. The permissions are kept in the .mdb file and maybe they're corrupted. (It can't be the network timing problem if it's all local.)

Good luck

 

[TheInsider]

Thanks for the input Quehay. I really wanted to re-write the database in MS SQL Server and have an ASP front end, but the time and costs involved didn't go well with my employer. So I got to use good old Access instead! Rob Marriott
rob@career-connections.net

 

[TheInsider]

Just to finish off this thread with a solution... if you have Access 2000, open your Access 97 database in Access 2000, without converting, and run a repair and compact. You will then be able to compact the database in Access 97 without any problems. Rob Marriott
rob@career-connections.net