multiuser split DB, replicating the FE?!? 1

[Phil4tektips]

I have a database which is going to have multiple users. I have split it, with my BE on a shared drive. I then replicated the FE so that the other users can have those. I can then update their forms, queries and reports from the Design Master.

I wanted to add another table as a development to the database. So I added the table in the design master and split the database again to get the new table to be in the BE. I then synchronised the replicas. This method failed and I couldn't open any of the old tables.

I noted that it has been said to Copy not replicate the FE's. How do you update these if they arent replicas? How can I add new tables to the BE that the
FE can see?

Is there a better way to set up what I am trying to do? Any help would be most appreciated.

Thankyou very much for any help,

 

[lupins46]

Replication was developed for data, not for design objects.
Updates to FEs are done just by copying the new version of the fe file to the users' machines. Replication is not really a consideration where all users can see the data at all times.

You need to have your own development copy of the frontend and backend.
When you have completed a set of changes, you have to close the system down so that you can make the necessary changes to the live backend. Then relink your development frontend to the updated live backend and then copy the frontend to users machines to replace the old frontend.

There are (free) programs around which enable you to place a single master copy of the frontend file into a shared area and each time the user starts their app, it checks the local FE version with the 'master' and if it has changed, the new FE is copied to their local machine.

This is just one:

http://www.granite.ab.ca/access/autofe.htm

 

[earthandfire]

Personally, I use a batch file to run the FE. The batch file copies the latest version of the FE to the local machine and runs it. So the users don't have a shortcut to the FE, just the Batch File.

Hope this helps.

 

[Phil4tektips]

I've read up quite alot about the Automated FE updater, but I will most probably be restricted from installing any external software by IT security.

How does the batch file work? Did you write the code for this? It sounds practical and useful. Would this mean if I updated the BE and refreshed the links to the FE, then the batch file would copy this ok?

I will have 15-20 users situated in different sites, but have access to the same shared network. I would ideally like to update their FE from my computer and not have them have to copy a new version of the FE.

 

[earthandfire]

Assume the "Master Copy" of the FE is kept on a server e.g. K:\Masters\MyFE.mdb and that the local copy is C:\LocalMDB\MyFE.mdb then using Notepad:

Create a new text document
@Echo Off
C:
CD \
If Not Exist C:\LocalMDB\*.* MkDir C:\LocalMDB > Nul
Copy K:\Masters\MyFE.mdb C:\LocalMDB\MyFE.mdb
CD LocalMDB
Start MyFE.mdb
Exit

Save the file as MyFE.txt

make sure that you have File Extensions visible in Explorer and change the extension to .bat therefore the file is now called MyFE.bat (it should have a cog in a window as its icon)

Place a copy this batch file on each user's Desktop - remove any shortcuts on the user's machine to MyFE.mdb and tell them that to start the program they double click on this icon.

If you subsequently change the BE, and then make any necessary changes to the FE, simply replace K:\Masters\MyFE.mdb with your new version of the FE. The next time a user double clicks the batch file (s)he gets the new version. (You could email all users telling them to close the current db and restart it if you applied the changes during the working day).

Hope this helps.

 

[easyit]

you could save startup time by adding a check for the latest version.

echo off

c:
cd\ 
if not exist C:\FCM\ mkdir C:\FCM\

cd FCM

rem copy if a newer version exists
if not exist "FCM_UR_VERSIE_3F.txt"  copy "N:\CCC Facturering consumentenmarkt\rap_publ\80_urenregistratie\FCM_urenregistratie.mde" "c:\FCM\FCM_urenregistratie.mde"
					   
rem kopieer versie bestand indien nodig
if not exist "FCM_UR_VERSIE_3F.txt" copy "N:\CCC Facturering consumentenmarkt\rap_publ\80_urenregistratie\FCM_UR_VERSIE_3F.txt" "c:\FCM\FCM_UR_VERSIE_3F.txt"

rem kopieer databases naar C indien deze er nog niet staat
if not exist "C:\FCM\FCM_urenregistratie.mde" copy "N:\CCC Facturering consumentenmarkt\rap_publ\80_urenregistratie\FCM_urenregistratie.mde" "C:\FCM\FCM_urenregistratie.mde"

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\FCM\FCM_urenregistratie.mde" /WRKGRP "N:\CCC Facturering consumentenmarkt\RAP_PUBL\88_mdw\SYS_FCM.MDW"

exit

 

[LittleSmudge]

I wanted to add another table as a development to the database. So I added the table in the design master and split the database again to get the new table to be in the BE.

If you have split the FE & BE you need to add the new table to the existing BE

You cannot add the new table to the FE and then resplit because the FE will only have links to the BE, not the real tables. If you split that you can't then link to links because access won't allow you to DaisyChain links.


As for making links to the BE - the following will be of use to you

Public Sub DeleteTableLinks(Optional strConnectString As String = "")
'If strConnectString is omited all links will be removed
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If tdf.Connect <> "" Then 'Check for linked tables
        'Check for pointed links
        If InStr(1, tdf.Connect, strConnectString, vbTextCompare) > 0 Then
            'Removing links
            DoCmd.DeleteObject acTable, tdf.Name
            DoCmd.Echo True, "Progress: Deleting link to table " & tdf.Name
        End If
    End If
Next tdf
End Sub

Public Sub RemakeTableLinks()
On Error GoTo Err_RemakeTableLinks
Dim dbs As Database
Dim tdf As TableDef
Dim tdfCount As Long
Dim intCount As Long
Dim strLinkSourceDB As String
Dim strFile As String
Dim strPath As String

' Need to go get database from user
If CurrentUser() = "graham" Then
    strPath = "C:\Files\CW\Customers\DO\BOff\"
Else
    strPath = "\\172.16.1.251\ssd\sysdev\"
End If
strLinkSourceDB = ahtCommonFileOpenSave( _
                  DialogTitle:="Please select a Back End file...", _
                  Flags:=ConstHIDEREADONLY, _
                  InitialDir:=strPath & "\", _
                  FileName:=strFile)

'Open source DB
'MsgBox strLinkSourceDB
Set dbs = OpenDatabase(strLinkSourceDB)

'Counting tables in the source DB
For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
        'Do not link to the System tables
        tdfCount = tdfCount + 1
    End If
Next tdf
'Check all tables in source DB (dbs)
For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
        'Do not link to the System tables
        'Creating links
        intCount = intCount + 1
        DoCmd.TransferDatabase acLink, "Microsoft Access", strLinkSourceDB, acTable, tdf.Name, tdf.Name
        DoCmd.Echo False, "Progress: Linking table " & intCount & " of " & tdfCount
    End If
Next tdf
'Close source DB
dbs.Close
Set dbs = Nothing

Exit_RemakeTableLinks:
DoCmd.Echo True
Exit Sub

Err_RemakeTableLinks:
MsgBox Err.Description, , "mdlLinkedTable, Sub RemakeTableLinks " & Err.Number
Resume Exit_RemakeTableLinks
End Sub

Edit the strPath = " part to suit your own needs.


'ope-that-'elps.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Phil4tektips]

Excellent advice by all thankyou.

I've just had some internal advice today, and it works for my application. That is to have a split DB with the FE/BE residing on a shared drive. Each user just has a shortcut to the FE, and they all can use the one copy. I've tested it and it works.

I'm guessing that most will cringe at the setup because of performance implications, but for the size of this application it seems best.

When I update the FE or BE I just update the admins version kept on the hardrive, check its working properly and then replace the shared drive version with the updated one.

The users keep their shortcut on their desktop and theres little hassle to them.

What do people think?

 

[lupins46]

SOrry to be a wet blanket here but your proposal is almost guaranteed to result in a corrupt database at regular intervals. So be absolutely sure you have frequent and sound backups of the backend.

 

[easyit]

I agree with Lupins46, you should definitely not go this way.

I will explain a bit more on how I maintain my multi-user DB's, of which some incorporates 80 users simultaneously.

When I want to add a new table, I just do so. I don't ask users to leave the app, since adding a table doesn't interfere (changing a table is another matter - than you need exclusive access).
When I'm finished with the work on the front-end, I click a menu button that disables the shift key, click another button th create the mde file (on the shared server). When done I again click the shift button, to re-enable it.
Two more things need to be done then:
1) change the name of the version file (***3A.txt becomes ***3B.txt)
2) change the *.bat file. In the batch file the old version file is checked, so simply find and replace 3A with 3B. I use a letter/number combi because it makes search & replace easier
In all deploying a new version takes less then a minute - if the compiling the mde doesn’t take to long.

By the way, my users do not have a shortcut to the mde file, instead they have a shortcut to the batch file that is also on the server. This guarantees me that when I change the batch file everybody will have the proper file.

I was bit hasty, so below is the batch file again in a more general form. This system works very elegant and is easy to use. The only trouble I have had so far in two years is a corrupt mdw file. But even this is easy to solve, change the startup link in the batch file to point to a backup mdw file and your back in business (in the corporate network I have no admin rights to the server, so I can’t delete the locking file myself). The only thing that I haven’t solved is the proper closing of the command window. Using API didn’t do the trick, it worked alright but in about 20% of the startups the database would freeze.

Let me now if you need more info on this subject, I’m glad to share.

echo off
c:
cd\ 
if not exist C:\FCM\ mkdir C:\FCM\

cd FCM

REM check the version file to see if a newer version exists
REM and copy the front-end if  necessary
if not exist "My_versionfile_1A.txt"  copy "N:\public_server_folders \MyCompiledFrontEnd.mde" "c:\FCM\ MyCompiledFrontEnd.mde "
                       
REM Now also copy the version file if there is a newr on the server
if not exist "My_versionfile_1A.txt"  copy "N:\public_server_folders \MyCompiledFrontEnd.mde" "c:\FCM\ My_versionfile_1A.txt "

REM If the mde file is not present on the local folder, than make a copy
if not exist "c:\FCM\ MyCompiledFrontEnd.mde " copy "N:\public_server_folders \MyCompiledFrontEnd.mde" "c:\FCM\ MyCompiledFrontEnd.mde "

REM Now start up the Database. Make sure the paths are correct:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "c:\FCM\ MyCompiledFrontEnd.mde " /WRKGRP " N:\public_server_folders\SYS_MyWorkGroupFile.mdw"

exit

 

[Phil4tektips]

Excellent. I've got that code working that easyit gave me.

At first it was failing because I was trying to write to the Crive which is restricted by IT Security. So I am writing it to the users hardrive instead. This seems to work.

Thanks to everyone for their valued time and efforts in proposing the best solution.

 

[easyit]

Phil,

Just to let you know, if the user hard-drive is a network location, you can get serious performance problems. I have had similar trouble, but solved it by using a folder that is already shared on the local drive. Usually there is one, just gotta find out which one - or talk to the ICT dep. My folder is now somewhere inside the programs folder...

EasyIT

 

[JeremyNYC]

I've got documentation for a similar thing I've used in the past in the developer section of

http://www.abcdataworks.com.

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org