Solved

Multi-User Access Database Management HELP!

Posted on 2014-03-19
12
578 Views
Last Modified: 2014-03-20
I have created an Access 2007 database that we are using to manage employee vacation time. When I created it the intention was to have a max of 2 users in it at one time. I was asked to open it up to management which forced me to split the DB.

Among a million little issues that I am having the biggest is managing frontend copies.

1) I send out specific instructions to go to a folder copy and paste the frontend to their desktop and it never fails. There’s always one that rode the short bus. This is causing me issues when I make updates. Suggestions?

2) I sometimes want to compact and repair the backend but with so many people connected through their back ends it locks everything up. Is there something I can do here? I created a feature that kicks everyone out of the frontend when I change a value in a table which works fine with one exception. If the user has the database open then walks away from their desk and locks their PC then it won’t kick them off until they unlock their PC.

3) Lastly, since I have a team of Admins of about 2 or 3 that need access to the different queries and tables in the backend that are not linked on the frontend should I make another copy or would it be okay to put all of my original queries, forms and such back on the backend and work from there?

Any assistance with this would be much appreciated.
Thank you
0
Comment
Question by:spaced45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39941181
Hi,

I create a version table that is linked and opened when a user opens the front end.  Each front end I distribute has a version.  When the front end opens, the versions are compared and if the front end is < the  shared version then I send a message to the user that the version they are using is obsolete.

This has an added benefit of leaving a table open so that the data connection is maintained.

I would suggest you give the Admins their own playground - not allow them to use the backend.

Regards,

Bill
0
 
LVL 1

Author Comment

by:spaced45
ID: 39941192
Bill,

I was looking into something like this myself. Would you happen to have any code that you could share?
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 39941205
Hi,

Create and attach tVersion table with VersionID as text(50).  One row...
Set application title = tVersion.VersionID
Execute TestForUpdate() in Autoexec or other startup

Code:

Function TestForUpdate()
    Dim v, vAns
    If CurrentDB.Properties("appTitle") < DLookup("VersionID", "tVersion") Then
        vAns = MsgBox("Program needs to be updated.  This version can no longer be used.  ", vbExclamation + vbOKCancel, vTitle)
       
    End If
End Function
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:spaced45
ID: 39941210
Bill,

Very straight forward and doable. Last quick question. .mdb vs .accdb does it matter what format my front end is in? Every time I research the subject I see .mdb but not sure if that's better or if I'm just reading info regarding previous versions. Any thoughts?
0
 
LVL 1

Author Comment

by:spaced45
ID: 39941212
Bill sorry what I meant is does it matter what format my BACKEND is in
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39941216
Hi,

You can have mdb as backend with either mdb or accdb as front end - or a combination of both.  You cannot have mdb front end with accdb backend.

If your users have Access 2007+ then I would suggest an accdb front end.  There does not seem to be a compelling reason to change the back end from mdb as long as it's in 2003 format (not 2000).

Regards,

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39941218
Also, you can add a DoCmd.Quit in the If statement to prevent them from using an older version.

Bill
0
 
LVL 1

Author Comment

by:spaced45
ID: 39941222
Bill,

Perfect! Thank you for all the assistance.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39941224
Sure.  Anytime.
0
 
LVL 4

Expert Comment

by:gozoliet
ID: 39941331
Agree with above.
For version management we have a few systems, but two that worked well:

1) Instead of a user launching the front-end directly, they run a .cmd script (like a .bat file) that always copies the latest version.  We have to check to see that the database isn't already open as that particular app expects people to open many copies of the DB.

REM  Copying file from network folder to c: (ignore paths)

IF NOT EXIST c:\tv2k10.laccdb copy z:\tvdb\tv2k10.accde c:\tv2k10.accde

call "c:\program files\microsoft office\office14\msaccess" c:\tv2k10.accde

Open in new window

2) In a StartApp function on startup, we do a DB lookup for latest active version, and compare it with something in the DB (in our case it's a label on the "MainMenu" form).  When it's different, we actually call a VBScript which automatically does the copy and then informs user when it's ready to open again (never fails, some people open it too soon and cause the DB to get corrupt, so we have to have a shortcut that "forces" a new copy down in the start menu.. Never fails, someone's on the short bus and they call tech support and the instruction is "run that shortcut".  Never really had a problem with #1 though.  Since it works "most" of the time, nice thing is we can easily "roll out a new version" without doing much more than flicking a checkbox.

Dim vBuild

On error resume next
vBuild = Wscript.Arguments.Item(0)

If Err.number <> 0 then
   Msgbox "The script requires a build identifier (like '2_1_0') as a command line parameter", vbCritical
Else
   On error goto 0
   If MsgBox ("TV2K Upgrader is ready to install TV2K version " & vBuild & ".", vbInformation + vbOKCancel, "TV2K Upgrader") = vbOK Then
      Set fs = CreateObject("Scripting.FileSystemObject")
' Parameters for CopyFile: From file, To File, Overwrite (t/f):
      fs.CopyFile "\\tvwonder\dev\mediabase\Source\TV2K10." & vBuild & ".accde", "C:\TVDB\TV2K10.accde", True


      MsgBox "You can now restart the database", vbInformation, "Upgrade Complete"
   End If
End If

Open in new window

Just some examples if they help.
0
 
LVL 57
ID: 39941720
You may want to look at Tony Toews "Auto FE Updater".  While it's not free any more, it's well worth the price.

It's a bit different in what's been discussed so far in that it is a "Launcher" style setup where an executeable runs and then starts up your DB.  This allows for all kinds of things, besides checking for version updates and automatically copying down a new versions.

http://autofeupdater.com/

Jim.
0
 

Expert Comment

by:Billxxxx
ID: 39942127
We had the same issue. What I did was store a copy of the front end in a different location on the network drive. F:\Common front end. After adding new code etc. I would first do a back up with the current version of the front end and then copy it to the specified folder F:\Front End  Back ups\Version 44 etc: On every one's desk top I installed a small program in VB that copies that file to the current user's computer  and updates them to the latest version of the front end .  This works great.  

The back up is very important. There have been a few times when the code or changes I made caused issues and I had to restore from the back up.   I tend to run the new front end for a while before I send a message to all users to update the front end. Unless the changes I made are critical, then even if the use's do not get my message, they can continue with their older version without any problems.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

734 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