Solved

Multi-User Access Database Management HELP!

Posted on 2014-03-19
12
567 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
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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now