asked on

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.

Andrew Porter

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?

wrt1mea

ASKER

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".

Thanks

Jeffrey Coachman

<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)

wrt1mea

ASKER

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?

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wrt1mea

ASKER

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?

Simon

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

wrt1mea

ASKER

How?

Simon

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)
    Else
        fOSUserName = vbNullString
    End If
End Function

Open in new window

Scott McDaniel (EE MVE )

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.

wrt1mea

ASKER

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.

wrt1mea

ASKER

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.