Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52

    Splitting Starter Leaver MS Access DB into Front End & Back End considerations


    Hi
    I have spent some time and developed a MS Access 2010 DB and now find I need to split the database into FE and BE.
    See https://www.accessforums.net/showthr...737#post323737

    I have designed the database normalised so that I have the main Tbl_Starter_Leaver and 4 other ancillary tables in order to avoid locking conflicts.

    The issue is that I have 10 or so data input leads who will enter starter/leaver data.

    I have read up a lot on splitting and deploying the front and to users. It feel impractical to deploy every time there is a change to the Front End.

    Assuming I have to split the DB which I think is the right way according to my research, this only leaves the question of where to place the Front End.

    I would like to ask if it plausible to have both the Front End and Back End sitting the same place i.e. :
    Otherwise I run the risk of upsetting a lot of Mgrs or have to go and do this manually !!.

    \\MyNetworkDrive\\Database\Starter_Leaver_BE.accdb
    \\MyNetworkDrive\\Database\Starter_Leaver_FE.accdb

    All users can access the Front End and using a shortcut link to that Starter_Leaver_FE.accdb

    Somehow I get the feeling that this misses the point and does not address the record locking issue.
    But If the Tables are normalised and the no of users minimal then this eliminate or certainly limit the locking problem right ?

    Would this work... ?.

    Thanks in advance.
    Last edited by mond007; 07-19-2016 at 05:23 AM. Reason: no of users changed.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    All users can access the Front End and using a shortcut link to that Starter_Leaver_FE.accdb

    Somehow I get the feeling that this misses the point and does not address the record locking issue
    Splitting the db is the necessary thing to do, but this does miss the point on two levels - each user needs a copy of the front end on their machine - sharing a front end will lead to corruption. The record locking is nothing to do with the front end and all to do with the back end.

    My issue is that most, if not all mgr, do not have access loaded on their PCs (by default) and it would be a IT call to get it loaded.
    You can provide users with a runtime version of access which avoids a licensing issue. IT may still need to load it but you can send out a .exe file which will install it automatically (subject to IT policy on users installing software). Note that even if managers used a front end on the server, they would still need to have access (runtime or not) installed - they wouldn't be able to open an excel file if they didn't have excel installed, for the same reason.

    You really should have considered these issues before starting your development as I suspect you will now have a number of things to fix.

    • record locking
      • a record is only locked whilst a user has it open. i.e. if a user has a form displaying a list of personnel, the only record locked is the one they have currently selected.
      • whilst a record is locked, other users can still view it (i.e. in their lists), they just can't make changes - how often are two users going to be wanting to change the same record at the same time? If your forms show the record selectors, users will see a symbol to indicate the record is being edited, otherwise you may need some code to do the same
      • adding new records will not affect record locking

    • number of users/performance
      • technically access will handle 255 concurrent users, you have mentioned 200 users and in practice you may find performance degradation after 20 or 30 - less if your tables are not properly indexed and/or you have poorly designed queries. You may need to add in some code to auto logout or disconnect the backend for users logged in but not doing anything - or use sql server/express as a back end.
      • network performance can also be an issue - locate the backend on the server, link your front end on your machine and see how it performs before going any further (note locating the front end on the server will probably be even slower)
      • consider using something like citrix or terminal server and locating both front end (one copy for each user) and backend on the same server - this will provide your best performance - it is virtually the equivalent to having both on your own machine.
      • forms which just have tables as their recordsource and utilise filters will be slower than forms which use criteria when opened to limit the number of records which need to come across the network.
      • Consider changing the backend to sql server (or express)
      • Access is safest on a hard wired network. If users are connected wirelessly and not using citrix/terminal server then there is an increased risk of corruption. If citrix/terminal server is not an option, also consider using sql server/express.

    • runtime
      • be aware that runtime is a very cut down version of access - no ribbon, navigation pane or shortcut menus. If your app relies on these things, you will need to create them yourself using VBA to load then when the app is opened.

    • maintaining front end
      • there are plenty of ways of doing this - easiest is to have a routine which checks current version with a version id stored in the backend. If they do not match, app is closed with a message to say later version exists. IT do not need to be involved. You can even automate it so users are provided with a shortcut which opens a small db which checks versions and replaces the old version with the new version before opening it.

    • maintaining backend
      • you will not be able to make any changes if anyone is logged in, so you will need some coding to advise users to close the app, plus more coding to force the close if they have forgotten to close before going home for the day - or see second point above.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't have to install Access on every PC. Install the runtime (it's free)

    you ALWAYS want to send users new FE when you make updates. All you do is copy /paste.
    BETTER is for all users to execute their FE from a network folder.(each having their own). Then you update all users at once by running a .BAT file that copies the FE to each user.

  4. #4
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Ok, so I think the fog is clearing.

    Firstly the no of users has been dropped from 200 to only 8 to 10 admin staff.
    So I think deployment has just been made easier.

    So if I read the above correct I will split the database which will create the following :

    \\MyNetworkDrive\\Database\Starter_Leaver_BE.accdb
    \\MyNetworkDrive\\Database\Starter_Leaver_FE.accdb

    I can then deploy the FE to each of the 8 users and send them Shortcuts for each user. i.e.

    \\MyNetworkDrive\\Database\USER1\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER2\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER3\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER4\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER5\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER6\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER7\Starter_Leaver_FE.accdb
    \\MyNetworkDrive\\Database\USER8\Starter_Leaver_FE.accdb

    This way future changed versions can be deployed really easily with no fuss by copying the main FE file to all the ones for each user. (ps Our network is very fast so no problems).

    The other thing mentioned is version control.

    I will create a table with a Tbl_Version_Release_No which contains the current release no 1.1 and ensure current latest version.

    Can anyone advise how this works in real life. I feel i have come all the way to the end of a good development and now face a downer by having to make these last minute decisions just prior to launch. In hind sight I don't think I would have done anything differently.

    There must be some VBA that looks at both Release Numbers and displays a message to say "A new versions exists - Please download/copy new version" ?. Does that mean I have two Tables one in the FE and all the other 8 in the USER1 to 8 directories. ?

    Thanks in advance.


  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Another method to deploy a FE to the users is I use a VB exe to copy the database down to the users PC, then find the version of MS Access on their PC and open the program for them. This way I can make a change to the FE db, save it once to the network and next time users click on shortcut (exe), they will get the latest changes. The first part of the code below checks to see if the program is open already. Then it checks to see if you have the directory already created and if not, will create it. Then checks for a version of Access and opens the program. I'm sure there are other ways to do this same thing not using VB but you get the idea. *cant get it to indent the code to show properly

    Private Sub Form_Load()
    On Error GoTo ErrTrap
    AppActivate "Your DB System"
    Form1.Hide
    MsgBox "The Your DB System is already open.", vbSystemModal
    Unload Form1
    Set Form1 = Nothing
    End

    CopyAndLoad:
    If Dir("c:\YourDB", vbDirectory) = "" Then MkDir ("c:\YourDB")
    FileCopy "\\Network_Folder\YourDB.accdb", "c:\YourDB\YourDB.accdb"

    If Dir("C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then
    Shell "C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE c:\YourDB\YourDB.accdb", vbNormalFocus
    Else
    If Dir("C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then
    Shell "C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE c:\YourDB\YourDB.accdb", vbNormalFocus
    Else
    If Dir("C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then
    Shell "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE c:\YourDB\YourDB.accdb", vbNormalFocus
    Else
    If Dir("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then
    Shell "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE c:\YourDB\YourDB.accdb", vbNormalFocus
    Else
    MsgBox "Could Not Find Microsoft Access Progrm. Please contact Your Support Info.", vbSystemModal
    End If
    End If
    End If
    End If
    Unload Form1
    Set Form1 = Nothing
    End
    Exit Sub
    Resume

    ErrTrap:
    Select Case Err.Number
    Case 5
    GoTo CopyAndLoad
    Case 70
    MsgBox "Could Not Copy Current Version", vbSystemModal
    Unload Form1
    Set Form1 = Nothing
    End
    End Select
    End Sub

  6. #6
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Great I really like that solution to deploy FE.

    Ok, I fear that our PCs are locked i.e. c:\ drives are locked, hence the use of the network drive.

    I am still a little unclear about how the VB exe solution get round the 'Version No' problem and puts up a message to say "incorrect version" but I assume no version checking required as it copies the FE every time.

    The solution that I think I prefer is to copy the FE to the users directories every time there is a change and since the DB is small I can do this myself (an automated solution can come later).

    I have read somewhere that its best to have a Tbl_Release_no in the Back End also Front End and simply check the version no and throw up an error when indifferent.

    I am not sure where this Table in the Front End would live ?
    (Perhaps I can worry about deployment when I have tested all the multi-user/record locking functionality!!).

    For now I can copy the FE manually when the FE changes.

    Thanks in advance.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    To prevent users from using an older copy of a FE db, I include an invisible version number field unbound on the first Form that opens that has the version number as the control source(the version number is just what I want it to be). I also have a table tblVersion that has one field containing the version number. Each time they open the database, I check to see that the version number on the form is the same as the one in the table. If it matches the form opens, if not I pop up a message saying wrong version and to use correct one, etc. and give them Exit button. So after you update the database, just change the version number in the field on the first form and in the table.

    As you said, if you are copying the FE each time and they have no way to use an older version(sometimes they copy the FE to their "work" folder or Desktop), then you might not need to check for a version.

  8. #8
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Great, I like the #5 post solution with the code but am unsure of a few things.

    a) How do you ensure after you have split the database that the Main Menu Form is the first form launched ?.

    b) I understand where to put the Form_Load code - this would be in the Main Menu form section.

    c) I am not what you mean by "next time users click on shortcut (exe)" - where and how do I create the .exe ?.
    Does it mean I created a Starterleaver.exe if so how ?.

    Can I guess that you are creating a Vb.Net with Visual Studio Express or whatever program that has a Form and a button that runs the code ?.
    This would then yield a StarterLeaver.exe which has the CopyAndLoad code ?. I think....

    I do also like the #7 post way of having the TblVersion and a Version field in the form. I like the idea of putting a "Release No" i.e. 1.1 and then increment for every new version ?

    Thanks in advance.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    It was actually done by another person using VB6 but you can use whatever to create the exe. The basic idea is to:
    1. Check to see if the program is already open (Optional)
    2. Check to see if the local folder is created and if not, create it for them
    3. Copy the database to that local folder, overwriting the one in there already.
    4. Find the version of MS Access on the users PC and opening the local copy of the database.

    a) This is in your FE database, click on Office button top left, Access Options, Current Database, Display Form.
    b) this is in the VB exe code, not in your Access database.
    c) Again that code goes into a VB exe. I am not a VB expert. You might be able to use a bat file or some other way.

  10. #10
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi I am struggling code implement the code suggested in post #5.

    I am trying to create the .exe in Visual Studio Express.

    a) I have created a Form1
    b) Please a button on it
    c) Pasted the code into it.
    There a lot of compile errors but I am trying to wade through them.

    e.g. I have had to replace 'Unload Form1 with Me.Dispose() etc... most errors I have worked through.

    It would be handy to know what package was used to create this .exe

    Thanks in advance.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What are you struggling to do? Are you having problems splitting your DB or are you having trouble automating the distribution?

    The point of the VB in post #5 is to have VB script execute and copy the FE file off of a shared folder onto the client's local drive. Then, the code determines if the client has Access 2007 32bit or 64 bit (can't remember if there is a 2007 64) or if the client has Access 2010 32 bit or 64 bit installed. It then uses that Access exe to open the file it copied onto the local drive.

    Some developers like to run script that always copies a fresh new FE file to the local client.

  12. #12
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Ok so the split has worked fine.

    Its the deployment that is the tricky bit.

    I now have the following code which I am testing.

    Code:
    Public Class Form1    Private Sub btnLaunchBusinessPlanningDb_Click(sender As Object, e As EventArgs) Handles btnLaunchBusinessPlanningDb.Click
            On Error GoTo ErrTrap
            AppActivate("BP Planning by PT_dept")
            Me.Hide()
    
    
            MsgBox("The Your DB System is already open.", vbSystemModal)  ' vbSystemModal
            Me.Dispose()        'Unload Form1
            'Set Form1 = Nothing
            End
    
    
    CopyAndLoad:
            If Dir("c:\BP Planning by PT_dept", vbDirectory) = "" Then MkDir("c:\BP Planning by PT_dept")
            FileCopy("\\myNetWork\BP-MasterDashboard Source\BP Planning by PT_dept.accdb", "c:\BP Planning by PT_dept\BP Planning by PT_dept.accdb")
    
    
            If Dir("C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then
                Shell("C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE c:\BP Planning by PT_dept\BP Planning by PT_dept.accdb", vbNormalFocus)
            Else
                If Dir("C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then
                    Shell("C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE c:\BP Planning by PT_dept\BP Planning by PT_dept.accdb", vbNormalFocus)
                Else
                    If Dir("C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then
                        Shell("C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE c:\BP Planning by PT_dept\BP Planning by PT_dept.accdb", vbNormalFocus)
                    Else
                        If Dir("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then
                            Shell("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE c:\BP Planning by PT_dept\BP Planning by PT_dept.accdb", vbNormalFocus)
                        Else
                            MsgBox("Could Not Find Microsoft Access Progrm. Please contact Your Support Info.", vbSystemModal)
                        End If
                    End If
                End If
            End If
            Me.Dispose()
            'Set Form1 = Nothing
            End
            Exit Sub
            Resume
    
    
    ErrTrap:
            Select Case Err.Number
                Case 5
                    GoTo CopyAndLoad
                Case 70
                    MsgBox("Could Not Copy Current Version", vbSystemModal)
                    Me.Dispose()            'Unload Form1
                    'Set Form1 = Nothing
                    End
            End Select
        End Sub
    End Class
    The 'Set Form1 = Nothing does not compile.
    I have also had to use me.Dispose rather than Form1.Unload etc...

    I am just testing now. and will post again if there are any issues.

    Thank you.

  13. #13
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Ok so the 1st hurdle is that the

    AppActivate("BP Planning by PT_dept")

    Is always true hence never stepping through to the rest of the code. This implies that the DB is still open when its not!.

    I can not test the rest until I get past this.

    Thanks

  14. #14
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Nearly there : the following error just at the precipice of getting it working.

    Click image for larger version. 

Name:	Access_error.jpg 
Views:	14 
Size:	36.8 KB 
ID:	25959


  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It is important to understand that the code in post #5 does not belong in an Access file. You would place this script in something like a .VB file. You would then distribute the .VB file to your Users. When they execute the script, it copies an Access FE file from the share. It will bring a new copy every time the user opens the FE file because the VB script manages the opening of the Access FE file.

    An alternative approach is to have the Access FE file manage the whole thing. So, the user clicks a shortcut to the Access FE file and that Access file determines (via a VBA module) if it needs to copy over a new version of the FE file.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-01-2016, 01:13 AM
  2. Replies: 1
    Last Post: 05-11-2016, 08:14 AM
  3. Replies: 4
    Last Post: 01-14-2016, 11:03 AM
  4. Replies: 4
    Last Post: 10-03-2014, 11:57 AM
  5. Replies: 5
    Last Post: 05-27-2013, 09:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums