Software

Ask something
Question Locked

Unable to compact a database

0 helpful
7 replies

by jordan1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello, around September of of 2007 I inherited a database to maintain. Ever since I started I was unable to compact the database I keep getting the error message "Records can't be read, no read permission." I found some code in one of the forms that I think may be the culprit. Would you please read and let me know if and what I can try to edit this code. I do have the password to get in and it does enable me to write data but doesn't allow me to compact when logged in under the user mentioned in the code below. Thank you so much I understand if you cannot help.

Private Sub Part_description_DblClick(Cancel As Integer)

If DBEngine.Workspaces(0).UserName = "Gatekeeper" Then
DoCmd.RunMacro "CR's.Change request"

ElseIf DraftValue = "yes" Then
DoCmd.RunMacro "CR's.Change request"
Else
DoCmd.RunMacro "CR's.Change request RO"
End If
Dim stDocName As String
stDocName = "Change requests"
DoCmd.OpenForm stDocName, , , [Number] = [Forms]![Cr's pick list all]![Number], acFormReadOnly

End Sub

7 replies

by Former member

As you work with your database, you might notice that the file size of your database increases. While that is a natural result of adding records, there is a compact feature in Microsoft Access that helps keep this file size to a minimum. By removing unused spaces in the database file, the compact feature reorganizes the database file into a more efficient and compact size. This process is entirely safe and will also help large databases run faster. In Access 2000, this process can be set to automatically compact every time you close your database. Whatever version you are running, it is a good idea to periodically compact your database. This Access tip shows you the few steps needed to run this function.
Access 2000

To manually compact a database:

1. With your database open, click
2. Tools. Click
3. Database Utilities. Click
Compact and Repair Database.

To compact automatically when you close a database:

1. With your database open, click
2. Tools. Click
3. Options. On the Options window, click the
4. General Tab. At the bottom left of this window, click the check box option called
5. Compact on Close. Click
OK.

Now your database will compact itself each time you close the database. Keep in mind, this option is only available in Access 2000.
Hope this helps you.
http://www.emsc.nysed.gov/irts/STEP/2004/downloads/compact-a-database.html

Please post back if you have any more problems or questions.

by robo_dev

What database are you using?

What database role does this "Gatekeeper" userid have?

Depending on the database, the ID would have to be assigned the appropriate permissions, such as DBO authority.

by jordan1

Hello,the user ID has the role of read and write permission so if someone accesses without this user ID and password they can only read everything but cannot write. I do have access to this logon but even when logged in and when I try to compact I get an error message that I only have read only permission it states that the owner is "Engine." I have totally copied over everything to a new database thinking this would now make me the owner of the database but I cannot export from this read only table. I keep getting an error message that me and someone else are accessing the database at the same time and no one else is accessing it when I try. Not sure what route to take the only thing I think of is to some how gain access to the .mdw file where all the security was set up but I do not know how to do that. Also, this is all done in Access 97. Thank you,

by robo_dev

Not sure bout access 97, but in Access 2003 you goto 'Tools> Security' to be able to adjust roles/permissions.

Compacting an Access97 database is more difficult.

http://mvps.org/access/general/gen0013.htm

Be sure to make lots of backups, because Access databases get corrupt very easily.

by jordan1

Hello, I have already given that user name ownership of everything but the only thing I cannot grant ownership to is the database itself that has to be done by the person who created the database and that person seems to remember nothing. So I think at this point there is nothing else I can do but to just let this thing die. Thanks again,

by bcgumbert

If the database is Access 97 you might want to covert it to the later formate. There will be some issues with some of the VBA commands since Microsoft usually changes these from one version to the next. The last time I had to do a conversion was from Access 2.0 to Access 97. There is a conversion task usually in the tools section of the main menu. Here are a couple of links for your to look at. This one is some textual information on the subject.

http://office.microsoft.com/en-us/access/HP051863711033.aspx?pid=CH062526001033

This one is a tutorial on Microsoft's web site of this topic. It is aimed at Access 2003 but the concepts should help.

http://office.microsoft.com/training/training.aspx?AssetID=RC011276151033

The conversion should be fairly easy. However you may need to test the converted application to ensure everything still functions correctly. The data is not the problem it is usually some of the VBA code needs changed. the first link I gave you will detail most of the issues. The other thing you may what to do is look at the Microsoft Technet site or the MSDN site for technical article on any other issues you might have.

This discussion is closed to new replies.