Page 1 of 4 1234 LastLast
Results 1 to 15 of 50
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Database "splitting"... some questions and concerns

    Experts:

    I need some advice/recommendations with respect to "splitting a database". Allow me to provide some background first.



    Background:
    - I work in a government entity and we recently supported another team with redesigning/developing their database.
    - The number of database users is approximately 5-6 people.
    - In the previous version, the database was *NOT* split and it allowed "multi-user" login; even though, the administrator indicated that there were times when some/all had to log out of the DB to make a change (no exclusive rights).

    New process:
    - As part of the transition from old to new database, we maintained the concept of keeping tables, queries, forms, reports, etc. in the same Access file (for now).
    - On day #1, however, I already ran into an issue where a dialogue box popped up indicating the DB had been corrupted. It repaired fine but the error certainly raised some concerns for me.

    Database splitting:
    - I am somewhat familiar with the concept of "splitting the database".
    - Before making the recommendation to split the DB, I'm brushing up on more details.
    - I came across the following article: http://www.fmsinc.com/microsoftaccess/databasesplitter/

    Concerns/questions:
    Based on the article, I can see the benefits of splitting the DB. At the same, I am also very much hesitant based on the information provided in para "Managing Front-End Databases on Each Desktop". Here's why:
    1. Given that we're still in the transition/user testing phase, I am 100% confident that we will have to make additinal changes (e.g., modifying forms, adding queries/reports, etc.).
    2. The article suggest that each user should have their own "front-end". This could be an administrative challenge...
    3. Would I have to email each user the new front-end? How can I ensure that they will utilize their new individual front-ends? (I don't have administrator privileges so I could not assist them in this effort).
    4. Alternatively, would it be feasible to place the front-end in a shared drive (I can access that shared drive) and then, e.g., add their lastname as suffix to the filename?
    5. Even if the latter is doable, I then would have to replicate the file several times every time I make a change to the master file, right?
    6. Finally, some existing import routine drop tables/re-import data into tables. Right now, they're imported into the single Access file. How could I force the re-imports to be added to the "backend" (vs. the "frontend").

    As you can see, I have several questions about the feasibility of the splitting. At this time, I eager to get some feedback pertaining to my six (6) "concerns/questions".

    Thank you,
    EEH

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Not splitting a shared database is just waiting for it to get corrupted.

    Every user must have their own front end. there are numerous ways to manage updating front ends. Look around, there are many examples of auto updaters. Some download a new front end each time the file is opened and others check for a version number on opening and download if new.

    How could I force the re-imports to be added to the "backend" (vs. the "frontend").
    The tables are in the back end. A lot depends on what your doing. You can also have temp tables in the front end if data has to be manipulated before saving to the back end.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Multiple simultaneous user db really should be split.

    As for strategies to manage frontend updating, here is code showing how I used to accomplish. Unfortunately, computer updates no longer allow programmatic copying of files and I had to modify this code to only notify users with a MsgBox popup to copy new version to their local drive folder and code opens source folder. Due to lack of IT support, this was the best I could do.
    Code:
    Private Sub Form_Load()
            
    'Check for updates to the program on start up - if values don't match then there is a later version
    If Me.tbxVersion <> Me.lblVersion.Caption Then
        'because administrator opens the master development copy, only run this for non-administrator users
        If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
            'copy Access file
            CreateObject("Scripting.FileSystemObject").CopyFile _
                gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
            'allow enough time for file to completely copy before opening
            Dim Start As Double
            Start = Timer
            While Timer < Start + 3
                DoEvents
            Wend
            'load new version - SysCmd function gets the Access executable file path
            'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
            Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
            'close current file
            DoCmd.Quit
        End If
    Else
        'tbxVersion available only to administrator to update version number in Updates table
        Me.tbxVersion.Visible = False
        Call UserLogin
    End If
    End Sub
    
    
    Private Sub tbxUser_AfterUpdate()
    If Me.tbxUser Like "[A-z][A-z][A-z]" Or Me.tbxUser Like "[A-z][A-z]" Then
        CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials, Permissions) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "', 'staff')"
        Call UserLogin
    Else
        MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
    End If
    End Sub
    
    
    Private Sub UserLogin()
    Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
    If Not IsNull(Me.tbxUser) Then
        CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
        DoCmd.OpenForm "Menu", acNormal, , "UserInitials='" & Me.tbxUser & "'", , acWindowNormal
        DoCmd.Close acForm, Me.Name, acSaveNo
    End If
    End Sub
    

    Frontend links to tables in backend. All data entry/edit and imports done via links. If you want to replace all records in table, then delete them and import new set. How do you accomplish this now without disrupting work?

    I did build 1 multi-user db that worked without issues which really surprised me but there was zero code (no macros nor VBA). It rarely had more than 1 person at a time using but testing with 2 users at one time worked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    moke123 -- thank you for the feedback... I appreciate it.

    June7 -- thank you for posting the code. Based on our environment, I'm not 100% confident this would be something the administrator can follow. May I presume that I don't have to force users to copy the front-end to their desktop, right?

    For instance, most users access the shared drive. I could envision the following process:

    - Subfolder "Backend" (contains the backend database with tables only)
    -- DatabaseXYZ_be

    - Main directory (contains the frontend files)
    -- DatabaseXYZ_User_Bernard
    -- DatabaseXYZ_User_Davis
    -- DatabaseXYZ_User_Hendrickson
    -- DatabaseXYZ_User_Johnson
    -- DatabaseXYZ_User_Smith
    -- DatabaseXYZ_User_Sorensen

    In this case, the adminstrator could make regular changes to the "Master Frontend". Afterwards, administrators copies/renames the file 6 times and renames them as shown above.
    Besides that we have to rely on users selecting their frontend files (a "Duh moment"), do you foresee any other issues keeping the six files in the same directory? That is, the administrator has readily control of pushing updates (assuming no is currently logged into their file).

    Thanks,
    EEH

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    When you have a single Access DB shared between several users, there is a risk of DB corruption. When you split the database and share a FE between several users, the risk of corruption will increase at least for a level.

    When you have a working split database, and you need to upgrade it create copies of both FE and BE in separate location, connect FE copy with BE copy, and do all changes and tests there. Distribute new version(s) of FE, BE or both, only after all tests are OK.

    When BE was updated, then import all data from old BE into new one when nobody uses database, and then overwrite old BE with new one.
    When FE was updated, then:
    When nobody uses database
    1. Remove old version from download folder to archive folder/Delete old version from download folder;
    2. Copy new version into download folder, and link to BE;
    3.
    a) Use some procedure or policy to replace all user FE's (it helps when all user FE's are on certain network resource(s);
    b) Use some FE event to check FE version (it helps when you use some naming convention like DBName_FE_v001.accdb in download folder, and you have the this name hardcoded into FE), and cancel database opening with message about new version on unsuccessful check;
    c) Send a mail to all users about new version being available, and let users bother with upgrading.

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Arvil:


    Thanks for chiming in... appreciate the details.


    We'll ensure that system maintenance will only be completed during certain times (early morning or at COB). Based on my last post/question, sounds like having several FEs in the shared drive shouldn't be an issue then, right?


    Tom

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    FE location isn't an issue so long it is in same LAN as BE.

    When admin replaces all user FE's with new version, it is also a procedure (but not programmed one)

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It can be done but the better way to go is to have a local copy on each users machine. You may take a performance hit using a shared drive. With no local copy, are the forms and reports subject to network traffic? Will a crash on the shared machine by one user affect the others? You need code to ensure no one opens someone elses front end while its in use.

    The simplist method to ensure they are using the most recent front end is to have a one record table in the backend which is simply a version number (a date works well). On open of the front end you check version number, throw a message box if outdated and quit the program. That forces them to update without question.



  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    What moke123 describes is what my example code does.

    A table has one record to store version number.
    Login form is bound to this table and form opens by default when db opens.
    A label on login form has version number.
    If record value is different from label caption, there is a new version of db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Shadow9449 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Feb 2017
    Posts
    38
    A very simple way that I deploy front ends to user workstations is by creating a small batch file with the following:

    Code:
    copy \\NetworkLocation\MyFE_Master.mdb C:\ApplicationLocation\MyFE.mdb /Y
    C:\ApplicationLocation\MyFE.mdb
    Instead of giving the users a shortcut to the FE to the app, I just shortcut to the 2 line batch file, which copies the latest copy to the client computer and then runs it. It takes an extra second for the app to load but it REALLY makes deployment super-easy. Any time I want to make a change to the FE, I just replace the MyFE_Master file and I am guaranteed that the users are using the latest version. I don't know if this is feasible in your environment but it's just another option.

    While on the topic of database splitting, I know you read the article but I just want to repeat the need to have a persistent connection to the database. This will make a noticeable performance difference.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I used to use a batch file (VBScript) to do the copy then I discovered how to have the copy code within Access. This is shown in my example. Don't ask me to explain why it works, it just did for 2 years until IT updated computers and then no programmatic copy would work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    All -- I apologize... have been in meeting entire afternoon and I'm just getting back to reading additional feedback. Allow me to respond one by one.

    1. June 7 - while I don't doubt your code works, I'm not entirely clear I should exactly implement it. We recently went live with the new database and I need to be 100% confident that any new changes won't have some negative impact on other operations. That said, would you be willing to provide me a very basic sample db so (with BE and FE) so that I can follow the logic? Thank you in advance!

    2. moek123 -- when you refer to network traffic, are you referring to "performance" concerns? Otherwise, the organization does do daily backups. You also speak about a similar process as suggest by June7. Would definitely also welcome to see a very basic example of your proposed process for checking forms.

    3. Shadow9449 -- I do NOT have administrator rights. So, I doubt that I would be able to have batch files executed in our organization. I like to simply the process but I'm just not sure this approach would work in our environment.

    Again, I thank everyone for contributing... having a sample file (#1 and #2) would greatly help me visualizing the process.

    Thanks,
    Tom

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    PMFJI - I'd give the local copy method points for not (usually) having to worry about Trusted Locations. IIRC, most user profiles are trusted by default, so you don't have to worry about administering to the issue. If you get a new user in the organization, you don't have to be concerned about permissions and trusted locations to their local profile as it's automatic. No?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I don't have a simple split db set up but if you want db that code is from, download from https://app.box.com/shared/r8nea07sng

    Hold down shift key when opening and change table links. Then it should run. It should create a record in Users table and you can modify your permissions to 'admin'.

    This is an older version and I think it still has the code I posted here, not the modification to MsgBox instead of copying.

    Ignore non-normalized table structure and other violations of conventional design. Everything works.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts and denormalize until it works."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I manage FE updates in a slightly different way.
    Each user has a desktop shortcut which opens a 'starter app'. This is a simple splash screen that checks the local version number of the main app against the network copy.
    If the versions are the same, the starter app closes and the main app opens automatically.
    If the newer version is newer, this is downloaded and starts automatically.
    The whole process is fast, seamless and ensures all are on the latest version with no vbscript required.
    The starter app can also 'replace itself' if a newer version exists on the network.
    See attached PDF for more info

    I also second the comment about a persistent connection to the BE to improve performance.
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 10
    Last Post: 08-08-2019, 11:05 AM
  2. Replies: 2
    Last Post: 03-04-2016, 04:32 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Splitting database: "There are no tables"
    By WithoutPause in forum Access
    Replies: 3
    Last Post: 04-27-2015, 10:46 AM

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