I have a big problem and it's stopping us from rolling out the db to our call-center... cliff-notes at the end...
I have a database that tracks customer calls.
The db files are located on the network in a folder that everybody has access to.
The db is arranged as such:
A backend that holds all the tables.
A front-end for Customer Service Reps with tables that are linked from the backend
A front end for Managers linked in the same way.
And a front end for our corporate folks which is also linked in the same way.
I've tried replicating, making mde's and creating a shortcut on each CSR's desktop. I've also made sure that I use the UNC to link the tables. I've also made sure the db's are shared (I think)
Cliff-notes:
when I have the front-end open on my desktop, and somebody else tries to open it, they get an error. "The file is already in use"
Am I doing something wrong Or am I missing some type of setting??
I guess I mis-spoke a little. Your database is already split, but as noted in the link, each user should have a copy of the front end on their PC rather than multiple people opening the same copy of the front end off the server.
Now I understand why BobLarson's FE update utility is soo useful.
That's going to be my project on Monday for sure!
So If I actually made .mde files from each of the front-ends, I'm assuming I would distribute copies of those instead.
Is that correct?
In this case, Is there any advantage to creating replicas of the front-end instead of .mde's?
Yes, you would distribute copies of the mde if that's what your front end was (that's what I use). Most of us use some sort of utility to automate the distribution. I wrote mine as a little exe, many use batch files and there are many like Bob's.
I've never bothered with replication, so I may not be the best judge of that. I don't see an advantage to them.
The only time that you need replication is when you are taking a db away from the master for a time and want to be able to recombine any data changes easily. For eample salesmen might have copies of the DB on their laptops and you would want to add any orders etc back to the main DB.
The important thing to remember with replication is that Autonumber fields don't increment in quite the same way but hold a time/date stamp info in them to avoid clashes.
To better understand when/where replication would be best practice,
I have an example situation;
If our call center was on a network that occasionally goes down, and we cannot afford to lose time due to the network, would replication be a better choice?
Or
Would it be wise to normally use the distributed mde, and in the case of a down network, advise our representatives to use a replicated copy.
I've tried to put a local copy of the front-end on two different computers.
I open the file on the first computer fine, but when i open it concurrently on the second computer, it says that my back-end is already in use.
Did I not split the db correctly?
2. Replication should be avoided if there is a better solution (such as hosting an app remotely via Terminal Server).
3. Replication is ideal for laptop users who need to edit data out in the field and then come back to the main office and synch their changes with the "mother ship."
Just a little follow-up on the issue above to help anybody else who has the same issue.
I have the DB set up now where every user has a local copy of the FE.mde on their machine.
The problem was that Access was letting all users log on as "admin". And since admin was set to open exclusive in the User & Group Permissions, only one person could open it at a time; hence the "file already in use" error.
My solution:
Just change the User & Group Permissions so that "admin" does not open the database or any objects exclusively.
Auto Update of the Front-End:
Bob Larson's Front-End Autoupdater is extremely useful and it looks like it works like a charm, but it's actually not built for Access '97. Bob was kind enough to convert his copy back to '97 for me, but a few functions are not supported in '97 and would require a little reprogramming.
I want to take this opportunity to formally thank Bob for his time and effort.
It is a great solution, but it's just not for me.
As I'm not an expert in VBA, I decided to look for another solution.
This is what I found:
A batch file, (although maybe a little archaic) works perfectly!!
Furthermore, I have the batch file located on my server for easy maintenance.
The users execute a shortcut to it from their desktop, which copies the latest version of the DB to thier local machine and then runs the local copy.
I added a little code, which was NOT hard to figure out.
The HKFILE and HKPATH variables were added because my DB's have a dependency on a .dll that I created.
If you don't have any custom dll's, you can remove those variables.
Here is the source:
Code:
REM A batch file to update a MS Access database then run it under a secure workgroup
REM by Keith Harvey 09/2000 - harvey@hunterlink.net.au
REM **** USER ACCEPTS ALL RISKS WHEN USING THIS BATCH FILE ****
REM **** STOP MESSAGES, CLEAR SCREEN, KEEP USER INFORMED ****
ECHO off
CLS
ECHO.
ECHO Starting Your Front End. Should only take a few seconds
ECHO.
REM Note that the variables and the batch file name MUST be customised for each database
REM ***** ABOUT THE VARIABLES ******.
REM PGRM is the variable for the file name of your current Front_End.mdb.
REM When updating the version of the Front End simply change PGRM and resave the batch file
REM (Note: PGRM must also fit the LOCDEL naming pattern below so old versions delete OK.)
REM LOCDIR is the directory on the Client PC that will hold the working version of PGRM
REM FILNM is the prefix of the filename since a space is not recognised in the "set LOCDEL=" function.
REM LOCDEL is the pattern of files to delete in LOCDIR if the latest version
REM of PGRM can't be found (Note that the value of LOCDEL is case sensitive)
REM (Vital Note: TRIPLE CHECK the items you assign to LOCDIR and LOCDEL. Delete is
REM VERY unforgiving. NEVER EVER set LOCDIR to just C: or LOCDEL to *.*)
REM MSACC is the dos path to MS Access on the local PC.
REM SRVDIR is the directory on the Server to get the latest version of PGRM from.
REM WKGRP is the location of the access secure workgroup to use. (Note. Leave WKGRP blank
REM if you are using the standard MSAccess work group)
REM HKPATH (Stands for "Hook-Path") is the directory of system.dll (%WinDir%\System\)
REM HKFILE (Stands for "Hook-File") is the system.dll file
REM ***** THE VARIABLES *****
SET PGRM="filename v1.mde"
SET LOCDIR="C:\Documents and Settings\%username%\WINDOWS"
SET FILNM="filename "
SET LOCDEL=%FILNM%*.mde
SET MSACC="C:\Program Files\Microsoft Access97\MSACCESS.EXE"
SET SRVDIR="\\server\sharedfolder"
SET WKGRP=
SET HKPATH=%WinDir%\System
SET HKFILE="system.dll"
REM ***** COPY YOUR CURRENT FRONT END TO THE PC CLIENT *****
REM Check if the local directory LOCDIR exist's, if not then create it
REM If current version of PGRM does not exist in LOCDIR then:
REM 1. Keep the customers happy!
REM 2. Delete any old versions of PGRM from LOCDIR,
REM 3. Copy the new version of PGRM to LOCDIR
if not exist %LOCDIR% md %LOCDIR%
if not exist %LOCDIR%\%PGRM% echo Updating to the latest version of %PGRM%
if not exist %LOCDIR%\%PGRM% del %LOCDIR%\%LOCDEL%
if not exist %LOCDIR%\%PGRM% copy %SRVDIR%\%PGRM% %LOCDIR%\%PGRM%
if not exist %HKPATH%\%HKFILE% copy %SRVDIR%\%HKFILE% %HKPATH%\%HKFILE%
REM **** FINALLY, START THE LATEST VERSION OF THE FRONT END ON THE PC ******
START /MAX %MSACC% %WKGRP% %LOCDIR%\%PGRM%
REM **** CLOSE THE MSDOS WINDOW ****
The only time that you need replication is when you are taking a db away from the master for a time and want to be able to recombine any data changes easily. For eample salesmen might have copies of the DB on their laptops and you would want to add any orders etc back to the main DB.
Jet Replication is useful where you need to have multiple users updating the same data MDB in different locations while still being able to merge their edits back into one file. Any two Jet replicas are only one synch away from being identical, so when those laptop users come back into the office, they can synch their laptop replica with the shared replica in the office and then all the users in the office will have access to the data created/edited in the field by the laptop user.
The important thing to remember with replication is that Autonumber fields don't increment in quite the same way but hold a time/date stamp info in them to avoid clashes.
This is simply not true. When you convert an MDB with Autonumber fields to a replica, the Autonumber fields are changed from incremental to random. There is no time stamp involved.
This is simply not true. When you convert an MDB with Autonumber fields to a replica, the Autonumber fields are changed from incremental to random. There is no time stamp involved.
Thanks for clarifying that. For some reason I had got it into my head there was a date/time element involved. However it does not alter my point that the autonumber keys are not the same as in a non replication system. This shouldn't be a problem if you use autonumber keys as pure keys wthout any other meaning.
Originally Posted by Rabbie View Post
The important thing to remember with replication is that Autonumber fields don't increment in quite the same way but hold a time/date stamp info in them to avoid clashes.
???
This is simply not true. When you convert an MDB with Autonumber fields to a replica, the Autonumber fields are changed from incremental to random. There is no time stamp involved.
But converting an MDB with Autonumbers does not convert the Autonumbers to ReplicationIDs (i.e., GUIDs). And, in any event, the time component is used to generate the GUID, but is not itself embedded in the GUID (so far as I understand it -- I think GUIDs are useless in Access, so have never bothered to figure out how they work, except insofar as I have to use them in replicated DBs).
