How to allow users to successfully use a split Access 2007 Database

Posted on 2009-12-16
Medium Priority
Last Modified: 2013-11-29
I have a small database that I have split on an XP Pro network. All users have Modify or greater permission to the database. I have left the back end of the database on the network share that they have permission to. I have copied the front end of the database to my desktop and to their desktop. I have verified that I am not opening it exclusively and that in Access Options, Advanced and under Advanced, "Shared" is selected in default open mode and "Edited record" is selected under default record locking.

I can successfully open the database front, view records, make changes, and run the macro. When I have another user open the front end database, they can successfully open it (while I have it opened), but cannot run the macro, or view the forms, or reports, etc, without getting a "file is already in use error."

I split the database to allow users to enter data and view reports, but cannot get past this hangup. Please help! I am on standby and should be able to respond very quickly.
Question by:wrt1mea

Expert Comment

ID: 26066594
Have you tried leaving the front end on the server as well, and copying a shortcut to the front end to the user's desktops?

Author Comment

ID: 26066670
OK, I tried that. I placed a shortcut on my desktop and opened it successfully.  I placed a shortcut on another users desktop and it gave me the following error when I tried to open it (while mine was still opened) "Could not lock file".

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 26066693
<All users have Modify or greater permission to the database. >
On the front end, Back end, or both?

The Front end BD shold be an MDE file on each users machine.
(No design view changes allowed)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 26066732
I have a folder on the newtork share that they have Modify or greater to.

The front end is still a .accdb file. Is that the problem? I just asccepted the defaults from Access when I split the database.

What should i check or change?
LVL 18

Accepted Solution

Simon earned 2000 total points
ID: 26067041
Check this MS link:
You may receive a "file already in use" error message when you try to open an Access database that is located on a server

Your users may have modify but not create/delete rights on the backend folder contents.  They need minimum of create/modify for the .laccdb lock file.

Always worth checking from a user's login that they can create/delete a text file in the backend directory.

Author Comment

ID: 26067391
OK, I looked a little closer. I can get to the data know with no errors. But one user needs read only to the database to only be able to print reports.

I noticed that "use simple file sharing was checked" on the network share. I unchecked it and verified that the user has read & excute. what do I need to do to set the user to only be able to open the databse and view the report to print?
LVL 18

Expert Comment

ID: 26067855
You could give that user a different front end with just the menu and reports.

Author Comment

ID: 26068678
LVL 18

Expert Comment

ID: 26070100
Copy your frontend.
Remove the forms that allow data entry, just leaving the linked tables and the reports.
Ensure that you have a menu/switchboard form that allows the user to access the reports.
Set the database options to not show the database window on startup, not allow special keys (so they can't bring up the database window). Save as .accde.

This provides a reasonable barrier to the user editing the data.

An alternative is to define users and access rights and have them login to use your database so you can control which objects they can use. I generally do a lookup of the users' windows login username and use that to compare to my table of users and their individual properties.

Paste this in a new module if you want to get windows username:

test it by typing ?fosusername() in the VB immediate window. It should return your windows login name.
Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
        fOSUserName = vbNullString
    End If
End Function

Open in new window

LVL 85
ID: 26071357
Further, I would STRONGLY recommend that you NOT place the FE on a network share and allow users to share that same FE. You should deploy a separate FE to every user, and that FE Should be installed on their desktops. While some report success using a share, in the overwhelming majority of cases this causes problems among users, and should be avoided.

Author Comment

ID: 26072193
OK, I will try to edit the front end of the user that I just want to print reports (have have created a macro for that. Thanks for the advice on that.

I am currently NOT Sharing the FE on the network share. I have copied the FE and placed on individual users desktops. I wont be able to try the VB code until after lunch. In meetings this morning.

Thanks again. Suggestions are welcomed.

Author Closing Comment

ID: 31667022
Thanks for the support! Since this is morphing into a different issue, I am closing question and assigning points to the most helpful.

I will post question on permissions for Access so look for my questsions.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question