• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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

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.
1 Solution
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?
wrt1meaAuthor Commented:
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".

Jeffrey CoachmanMIS LiasonCommented:
<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)
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

wrt1meaAuthor Commented:
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?
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.
wrt1meaAuthor Commented:
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?
You could give that user a different front end with just the menu and reports.
wrt1meaAuthor Commented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
wrt1meaAuthor Commented:
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.
wrt1meaAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now