Database read only for non Admins? (Office/Access 2000 SP1A)

Topic

New Reply

I have come accross this problem before but solved it by making the Win2000 box log on automatically with a user
in the Admins group. The problem being that my Access application, complete with VBA code modules, will not
function properly on a computer unless a member of the Administrators group is logged on.
A mere power user gets the message (as soon as any VBA code starts to execute) that the backend database is read only.
It isn’t.
What does this mean?

Reply | Quote

Replies

It sounds like your permissions aren’t adequate for ordinary users, but is the problem Access permissions or Windows/network permissions? In other words, which administrators group are you talking about? I’ve seen problems when an application was installed by an administrator but it wasn’t made available for all users on that machine. Could that be the issue?

Oh, one other thing I discovered the hard way: If you maintain an ADO connection to the backend database from a form, and then you create a new connection and try to modify things, you can get that kind of error even if your *are* an administrator and are the only one touching the database … and even if your first connection is read-only.

If you only get the message from the code but a power user can edit the tables directly, then your code is the problem. What are you doing that requires additional permissions? Are you attaching or reattaching tables, setting values in the backend or what? Are you working with recordsets in code when you receive this error? If so, you can pass the username and password in a connection string for ADO and use that connection to do anything that requires admin permissions. In DAO, you can open a secured workspace and open the recordsets using a database object opened in that workspace so that you can carry out operations that require admin permissions. We use this approach in a number of places in our applications because some of our users have read-only permissions on the application, but there still needs to be stuff going on behind the scenes, like changing the apptitle.

Reply | Quote

I’m talking about the Windows/Network permissions – I am pretty much 100% sure I’ve got the Access User level security under control.
Taking up your first question: what do you mean by “making the application available for all users on the machine”?
The folder which contains both the back end mdb file and the front end as well is not password protected or unavailable for browsing for a power user.
My application is quite simple and clean in principle, and uses only ADO. I’m eschewing DAO to keep things neater.
It’s basically adding and deleting records from tables using ADO by interacting with forms. The connection strings have full authentication (username, pasword)
My first action is to click on a button that runs this event procedure:
Private Sub NewSaleRecord()

Dim cnnCurrent As ADODB.Connection
Dim rstSales As New ADODB.Recordset

Set cnnCurrent = CurrentProject.Connection
rstSales.CursorType = adOpenKeyset
rstSales.LockType = adLockOptimistic
rstSales.Open “Sales”, cnnCurrent, , , adCmdTable

With rstSales
‘ at this point – next line – the code stops running and I get the message that the database is read only.
.AddNew

![OrderDate] = Date
![OrderTime] = Time
.Update

intSID = ![SalesID]

End With

rstSales.Close

Set rstSales = Nothing
End Sub

As commented above, as soon as I try to add a new record to the table I get the read only message
if any Windows user other than an administrator is logged onto the workstation.

Reply | Quote

Have you tried setting the CursorLocation to adUseClient? I’ve seen something similar when I have forgotten to specify a client side cursor, even though the backend is Access.

Reply | Quote

I was afraid that might cause Access to explode.
However, as you obviously are more experienced that I, I’ll give it a burl and report back.

Reply | Quote

Just wear your safety goggles.

Reply | Quote

I recently set up a database in its own network folder for the first time, while every user could open the front end, when I split the database it wouldn’t work. The end solution was to reset the permissions in the folder. For whatever reason the users had all the required permissions for the folder, but they were not applied to new files in the folder, once the network guy had set permissions for the individual files and the folder we were away.

This was a Windows 2000 network.

Reply | Quote

I recently set up a database in its own network folder for the first time, while every user could open the front end, when I split the database it wouldn’t work. The end solution was to reset the permissions in the folder. For whatever reason the users had all the required permissions for the folder, but they were not applied to new files in the folder, once the network guy had set permissions for the individual files and the folder we were away.

This was a Windows 2000 network.

Reply | Quote

Just wear your safety goggles.

Reply | Quote

I was afraid that might cause Access to explode.
However, as you obviously are more experienced that I, I’ll give it a burl and report back.

Reply | Quote

Have you tried setting the CursorLocation to adUseClient? I’ve seen something similar when I have forgotten to specify a client side cursor, even though the backend is Access.

Reply | Quote

I’m talking about the Windows/Network permissions – I am pretty much 100% sure I’ve got the Access User level security under control.
Taking up your first question: what do you mean by “making the application available for all users on the machine”?
The folder which contains both the back end mdb file and the front end as well is not password protected or unavailable for browsing for a power user.
My application is quite simple and clean in principle, and uses only ADO. I’m eschewing DAO to keep things neater.
It’s basically adding and deleting records from tables using ADO by interacting with forms. The connection strings have full authentication (username, pasword)
My first action is to click on a button that runs this event procedure:
Private Sub NewSaleRecord()

Dim cnnCurrent As ADODB.Connection
Dim rstSales As New ADODB.Recordset

Set cnnCurrent = CurrentProject.Connection
rstSales.CursorType = adOpenKeyset
rstSales.LockType = adLockOptimistic
rstSales.Open “Sales”, cnnCurrent, , , adCmdTable

With rstSales
‘ at this point – next line – the code stops running and I get the message that the database is read only.
.AddNew

![OrderDate] = Date
![OrderTime] = Time
.Update

intSID = ![SalesID]

End With

rstSales.Close

Set rstSales = Nothing
End Sub

As commented above, as soon as I try to add a new record to the table I get the read only message
if any Windows user other than an administrator is logged onto the workstation.

Reply | Quote

It sounds like your permissions aren’t adequate for ordinary users, but is the problem Access permissions or Windows/network permissions? In other words, which administrators group are you talking about? I’ve seen problems when an application was installed by an administrator but it wasn’t made available for all users on that machine. Could that be the issue?

Oh, one other thing I discovered the hard way: If you maintain an ADO connection to the backend database from a form, and then you create a new connection and try to modify things, you can get that kind of error even if your *are* an administrator and are the only one touching the database … and even if your first connection is read-only.

If you only get the message from the code but a power user can edit the tables directly, then your code is the problem. What are you doing that requires additional permissions? Are you attaching or reattaching tables, setting values in the backend or what? Are you working with recordsets in code when you receive this error? If so, you can pass the username and password in a connection string for ADO and use that connection to do anything that requires admin permissions. In DAO, you can open a secured workspace and open the recordsets using a database object opened in that workspace so that you can carry out operations that require admin permissions. We use this approach in a number of places in our applications because some of our users have read-only permissions on the application, but there still needs to be stuff going on behind the scenes, like changing the apptitle.

Reply | Quote

I think it might be a file permissions problem.

When opening an Access database, if no other user currently has that database open, Access creates a .ldb file in the same folder as the mdb/mde file. If permission to create files in this folder resides with Administrators but not PowerUsers, it might explain why the latter can’t be the first to open the database.

Once this .ldb file has been created, the need to file creation rights disappears as all subsequent database users merely need rights to modify this file.

Of course, when the last person using the database closes it, the .ldb file will be deleted and thus the database becomes readonly again to all but the Administrators.

Reply | Quote

I think it might be a file permissions problem.

When opening an Access database, if no other user currently has that database open, Access creates a .ldb file in the same folder as the mdb/mde file. If permission to create files in this folder resides with Administrators but not PowerUsers, it might explain why the latter can’t be the first to open the database.

Once this .ldb file has been created, the need to file creation rights disappears as all subsequent database users merely need rights to modify this file.

Of course, when the last person using the database closes it, the .ldb file will be deleted and thus the database becomes readonly again to all but the Administrators.

Reply | Quote