Results 1 to 11 of 11
  1. #1
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Best Practices Question - Development Procedures for updating a split database

    All right, I'm just about to implement the new updated and split Access 2010 version of a database that previously was unsplit Access 2003.



    Part of my responsiblity is to write up the ISO procedures for future updates and support to the database, and I've realized that I'm not sure how that needs to work in practice.

    Copying and updating an unsplit database has very little danger, but updating and testing the frontend master could result in random changes to by live data. Not good.

    So, what are best practices for the procedures to check out, update and test a split database?

    Step by step, please, because my mindreading skills are merely average, and I don't have any practical experience with live production split databases.

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Dal, If this was a question from someone other than you, at your level of experience, I would answer without hesitation.

    However, since the question is from you it causes me pause.

    Either I am not understanding your question completely or there is a lot more to checking out a revised database than I thought.

    Am I missing something in your question?

    Dale

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm assuming you are removing the ability for people to access the 'live' (current, unsplit) database while you're performing your update.

    Are you trying to create an automated update process or just a list of things you have to do to make sure the 'new' database is accurate and that all designed functions work?

    The testing of table design, for instance, could be automated to make sure each table in your database has identical fields/data types but the actual testing of your forms/reports etc would likely have to be a manual process unless you have query objects driving your reports then you could check the values in the 'new' vs the 'old' query to make sure all the fields match as well.

    If you're looking to make this strictly a manual process that anyone can follow that's a different banana.

  4. #4
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    rzw0wr - As Dirty Harry Callaghan repeated, "A man's got to know his limitations." I'm an extremely experienced business analyst, and mainframe developer. I've done VB development occasionally since 4.0, and before that I worked in Borland OWL. Generally, all of those shops have had code repositories or equivalent procedures.

    In contrast, the limits of my experience in development on Access are these - very informal shops, unsplit databases, very circumscribed applications. One critical reason I'm hanging out on these forums is to port my varied experience from other applications, and from older versions of Access, over to the current gen (A2010). And I learn best by teaching, by answering questions that push my limits or that nail down the things I *almost* know. I've been on for about six weeks, and I think I've achieved that part fairly well.

    But there are still many aspects of Access development that I just plain haven't thought about.

    Here's our current method for enhancing the database -
    (1) copy the database to a test location (your own C drive, for example)
    (2) do your updates, test
    (3) verify your updates are working,
    (4) disable (temporarily rename) the production db,
    (5) back up the pre-modification production db,
    (6) port in the modifications,
    (7) back up the updated database, then
    (8) enable/rename the db back to the production name.

    Heres the obvious problem: now that the database will be split, when you copy the front end for updating, it's still linked to the back end. So unless you do something different, testing would be occurring against the production files. Not acceptable.

    So, what's the normal thing to do in an Access production shop? Obviously, you have to have some standard way to switch the connections before you start coding on your copy. How does that work?

    Once I realized that I had a big hole in my procedures, I started wondering what else I might be missing. I'm not worried about the method of testing itself, and I'm not worried about deployment of the enhanced db. (I expect Bob Larson's auto-updater code to be quite adequate for my needs.)

    I'm worried about whether there might be other aspects that I'm totally ignorant of. So, pretend it's a newbie question, because in this particular context, I'm just a smart newbie.

    rpeare - not really worried about testing - that part I can define pretty well. I'm working on the formal list of steps that replace step 1 above, pretty much at that level of detail.

    1A) Copy the production BE and Master FE to your test location
    1B) do (something) to the FE to tell it to use the test BE
    1C) anything else?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Step 1 would be to copy the front end AND the back end to a 'test' location
    use the linked table manager to re-establish the links (check the box that says 'always prompt for a new location') and point to the test folder/test back end.

    If you open the linked table manager there should be a path name that shows you where the source table is, so after you've re-established the links you can double check to make sure you're not still pointing to the 'live' database.

    Alternately you could delete all the linked tables in your front end, then import all tables from the test location/database. Personally this is the way I tend to do it because when you always prompt for a new location you have to do it for each table rather than one fell swoop.

    If you're super paranoid you can also rename the back end of the 'live' system to something new to make sure it's disconnected. Of course you only want to do that while nobody else is going to need access to the application.

  6. #6
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Hmmm. I suppose I could, on open of the FE, check the location of the current FE file. If it is not in the expected (live) location as per the ISO instructions, it could automatically disable update capabilities to the live BE, check for BE in the same directory, and/or query the user whether he/she intended to perform programming...

  7. #7
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    And, yes, I consider paranoia to be the only proper mindset for a system design professional.

    Paranoia during design and implementation phases, detached sense of humor (and occasionally sense of wonder) during support phases.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is what I do Dal, if I want links to be automatically refresh properly I have the front end and the back end in the same folder, or have the front end/back end in an 'expected' arrangement for instance the front end in folder c:\Test and the back end in Folder c:\Test\Data.

    Then when I open the database I automatically refresh the links based on the expected location of the DATA folder (currentproject.path & "\Data\")

    That's where I drop all 'linked' tables and re-import them based on the expected location. then there's no possible chance of touching the 'live' data.

  9. #9
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Hmmm. Can't have the back end in the same (sub)directory for normal operations, since that kills the whole purpose of splitting the database. Each user is going to have a local C: copy of the front end. So the setup might be like this:

    L:\longpath\XXXX - location of Front End Master
    L:\longpath\XXXX\Data - location of Back End Master
    C:\userpath\YYYY - Location of user front end
    ?:\anypath\ZZZZ - location of Front End Test
    ?:\anypath\ZZZZ\Data - location of Back End Test

    If I programmed a "setup test environment" routine, then it could clone the current production backend to a \Data subdirectory, detach from the production backend and attach to the test backend. hmmm. Programmer splash screen?

    There's no such thing as foolproof, but "generally automatic and easy for normal operation" is what I aim for.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My experiences:

    Split FE & Be. I would kick out everyone, copy the current production BE to my C drive, then allow everyone back in to the live dB.

    I would relink the FE to the BE on my 'C' drive........except once. I was testing using the live data. So after recovering from that (backups are good!), I added two unbound text boxes on the main form. They are at the top of the form - the upper text box is the name of the FE I am modifying/testing. The lower text box is the name of the linked BE.

    Shouldn't be a problem now... right??? Except I sometimes read too fast and see what I want to see. I was interrupted before I had relinked the text FE to the text BE. I glanced at the lower text box, saw what I wanted to see, and, well...... backups are good!

    While all of the linked tables reside in the same BE (right now), I check to see if all of the linked tables are on the 'C' drive. If all linked tables are on 'C' drive, the lower text box has a green background; if one or more linked tables are not on the 'C' drive, the lower text box background turns red.

    So far, seeing red has protected me..... so far ......

  11. #11
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    And the moral of the story is... Backups are good!

    Thanks for that color coding story - I can see a situation where the programmer has a test file open and needs to clone something from the production files. I believe I need to implement a test-version-color-change.

    See, that's why I ask this question now. I can implement pretty darn well anything useful, until I move this to production. Then, ISO rules apply. Whatever ISO rules I end up writing, that is.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Split Database Question - Will this work?
    By Jrbeene86 in forum Access
    Replies: 5
    Last Post: 08-09-2012, 06:47 PM
  2. Replies: 2
    Last Post: 02-25-2012, 06:29 PM
  3. Best Practices
    By Sinkerhawk in forum Access
    Replies: 2
    Last Post: 06-30-2011, 11:44 AM
  4. Form Record Source - Best practices.
    By ser01 in forum Forms
    Replies: 1
    Last Post: 06-11-2011, 11:32 AM
  5. Updating a Split Database
    By bobfin in forum Access
    Replies: 3
    Last Post: 08-10-2010, 11:14 PM

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