Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2016
    Posts
    8

    Unanswered: Developer question

    Hi everyone,

    As I'm not allowed to send my project home for security purposes I'm forced to work on the project during normal business hours (9-5).

    The only way I am able to develop the database is when everyone is out of it, which would either be really early in the morning, or after 5 o'clock.

    Neither are practical for me, as I won't be getting paid overtime. Anyway, pay is irrelevant at the moment.

    What I am trying to achieve or I guess find out if it's safe and plausible is...while someone is actively entering new records, I can somehow copy and paste the database into another folder...

    Open the database, complete the design changes (as all I'm trying to edit is design changes, nothing that would evidently affect the main table that people are logging records into). then somehow, when I'm finish, import the table from the original database into the database that I have been editing, so no records or time has been lost.

    I think I may have explained this incorrectly, I'm sure what I'm trying to achieve is fairly simple, but I am still learning!

    Many thanks in advance for all of your support.

    -Donatello

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This is obviously an app being used in a multi-user environment, and so should be split into a Front End, with everything except the Tables...and a Back End, with only Tables...is this the situation?

    Welcome to dBforums!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2016
    Posts
    8
    Quote Originally Posted by Missinglinq View Post
    This is obviously an app being used in a multi-user environment, and so should be split into a Front End, with everything except the Tables...and a Back End, with only Tables...is this the situation?

    Welcome to dBforums!

    Linq ;0)>
    The database is being used by about 15 or so colleagues.

    So to be able to edit the database while someone can be adding records I need to split it into a Front End and a Back End?

    How do I go about doing this?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Having it split will, of course, allow you to make design changes without disrupting your users, but that's not the really important thing, here! Having multiple users sharing a single, non-split database, sitting on a network drive, is the sure way to repeated episodes of corruption, as well as speed and timing problems! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

    Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past ten years have seen dozens and dozens of reports of non-split apps causing these kind of problems! The really insidious thing is that a non-split app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

    Here are two tutorials on Splitting a Database:

    https://www.fmsinc.com/microsoftacce...abaseSplitter/

    http://www.hitechcoach.com/index.php...atid=24:design

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so its a multi user db
    that means it should (already) be split into a separate front end and back end
    front end the user interface (forms, reports, user specific queries), and in an ideal world on the users desktop / own workstation
    the back end contains the data, any common queries, possibly batch programs on a server share somewhere
    mind you at 15 concurrent userrs be aware you may be hitting or about to hit the effective limit on concurrent users using JET as the default data store. at some point in time you may need to think of switching to a server back end. you may also need to start thinking of using unbound controls in forms in preparation for that move. you almost certainly need to start thinking about how you populate list boxers and combo boxes, how you display data and how you design forms. nor more dispaly all data think inmstead of openign a form then display a subset of the data and provide tools for the user to grab what data they need to see.

    you as developer have your own separate pair of front end and back end to do whatever the heck you need to
    when you are finsihed deve;loping, testign amd get soem QA signoff you then migrate the developement front end to a public share, make certain it points to the live back end and awy y'go.

    main pitfall is forgetting to change the target of the back end...

    there is code in the code bank (first page) on this forum which can handle making certain users are using the most recent version of the db.


    as to how you split it.
    copy the current mdb twice
    rename 1 to be your backend, 1 to be your front end
    ...being a boring old fart I tend to call 'em <appname>_data & <appname>_app
    eq branch_ops becomes branch_ops_data and branch_ops_app
    or
    branch_data and branch_ops_app

    go into the copy of the data and delete all forms, reports and generic queries
    save it

    go into the copy of the front end and delete all tables and queries
    then link to the external back end

    then copy both those files

    the first pair of data & app go back as the live version
    the second pair are your development version and if needs be can be on a local workstation or different server share to the live version
    again being the desperately boring person I am I'd call em somethign like
    dev_branch_data & dev_branch_ops_app

    then rename the current database so it can no longer be used, tell you users to use the new front end. naturally you will have to do this outside working hours or lock out the users whilst you do this. there is some code which I first saw in Litwin Getz & Gunderloy's Access Developer handbooks which could identify which users were still in a file, but that may not work with more modern versions of Access.

    the advantage of renaming the old current db is that if the worst comes to the worst the old app is sitting there and can be switched back almost immediately. ( I use the same technique when deploying a new applciation, leave the old version lurking with a new name just in case )

    take copious notes of any changes to queries and table design and IMPORT those from the development backend to the live back end, again users need to be locked out whilst this goes on. or be a smarty pants and store the SQL that performs those changes as a query or even another db. that way round you can apply all the changes to the structure, then apply any changes to data with a minimum of downtime.

    then when developing get used to taking lots and lots of backups (instead of relying on the system backup I just do a simple copy of the file periodically... ie whenever Im at a commit point that woudl really really piss me off If I had to redo it. (think get a complex multi join query to work, some fiendish bit of report code) that woudl take hours to redo AND every few hours

    before deploying a new front end either compact & repair your development front end then copy it over the live front end AND change the target table location (re link the db), or if you are a smarty pants use code to dynamically reset check or rest links to the back end, again Litwin, Getz & Gunderloy has code that does that. some argue that comapct and reapir can cause problems over the years and suggest you should import all object into a clean db which supposedly circumvents any legacy issues.

    as part of the ongoing maintenance of the application you need to compact and repair the live data to make certain the indexes are 'good'.

    as an aside if I have to run batch processes then I tend to use another front end that modifies the data in the back end.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2016
    Posts
    8
    All I can say is thank you so much for all your effort in responding to my query! It's greatly appreciated.

    I will be giving both your responses a proper read and think before taking action.

    Thanks again!
    -Donatello

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •