Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2012
    Posts
    20

    Unanswered: Need help with BE updating...new to Access

    Disclaimer: This may be a bit long to help understand the problem.

    I just got a job with the Dept. of Corrections in Michigan last year and have taken over the task of creating a database (first time using Access) for seven of us doing the same job. We screen individuals leaving prison to determine eligibility of certain things prisoners need on parole such as Birth Certificates and SSI applications. The tables hold information such as what they are eligible for, background information...etc.

    Myself and another individual created an Access database that pulls the offender info from the MDOC's records and we update this weekly so when people get transferred or their status changes, we will know. All of the other stuff we enter into the form manually, such things stated above.

    We created a back-end on the MDOC's shared drive. Every two weeks, seven of us dump our information onto the back-end using append and update queries and then also receive the information from the back-end using append and update queries to get any updates and information other workers have entered on different inmates.

    Once I add an inmate to the table, I enter his information into the form. Everything is working dandy. The other workers connect to the back end and now they have all of my screenings on their copy of the database that is stored on the back-end and I have theirs once I connect and so forth.

    The problem I am having is this:

    Once I go back to update some information on an inmate I have screened, he is changed on my copy of the database on my computer. I connect to server and it is updated on the back-end. Now, someone else connects to server and since the inmate I just updated isn't updated on their copy of the database, it overwrites all of the information back to the original information I entered before updating his form.

    Does that makes sense? I am very new to this and am in a bind.

    Another solution is, if we just have one copy of the database on the shared drive and we access that every time we enter data. How many people can access that database at a time and enter information on it?

    Thanks a TON for any help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by chadillac View Post
    Another solution is, if we just have one copy of the database on the shared drive and we access that every time we enter data. How many people can access that database at a time and enter information on it?
    1) Split your database on the FE/BE schema (there's an assistant in Access that can do it for you).

    2) All tables go into the back end that is stored on a network drive and is accessible by every machine running the front end. The front end has all the interface an functional objects (queries, forms, reports, modules, macros), plus attached tables that are linked to the "real" tables stored into the back end.

    3) Under 25-30 FE connected at the same time to the BE you won't have any problem. It can be a lot more, the limit depends on many factors that are difficult to expose and explain in a few lines.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The real problem here is that it would appear, from your reference to 'dumping information every two weeks'" and 'using append and update queries,' that you're using Unbound Forms, and Access is really designed to be used with Bound Forms. When used in the way it's designed to be used, and set up as Sinndho suggested, all the problems you're having are handled automatically, by Access, and in actuality aren't problems at all.

    As to your final idea, having one copy of the database on the shared drive for all to share when doing data entry, that is absolutely, positively the worst approach you could possibly take! Using that scenario is guaranteed, with no room for doubt, to produce more problems than you could ever dream of, including repeated corruption and data loss!

    If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.
    • You can create an EXE file which gives total protection to your code/design
    • You can distribute the db to PCs without a copy of Access being on board
    • Your data security if far, far better than anything you can do in Access

    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

  4. #4
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    chadillac

    I used the the FE/BE schema as suggested by this site. What fixed my issue (almost the same as yours) was to use the Clone method (found in this site). I have never had a problem with multiple users (up to 30) over a network that is not always reliable.

    Using this allows me to have live data available at all times. PM me if you want me to walk you through it.
    We tend to look at Linear paths which can lead us to a path of resistance!

  5. #5
    Join Date
    Apr 2012
    Posts
    20
    Ok, so am I understanding this correctly that I can actually have people enter data into the form without actually needing to have a copy of the Access on the FE on their computer?

    I would like to find out more about the bound forms so that I don't have to worry about this problem. Is there anywhere I can find a way to switch the forms over to bound?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem to me sounds like there are multiple backends
    We created a back-end on the MDOC's shared drive. Every two weeks, seven of us dump our information onto the back-end using append and update queries and then also receive the information from the back-end using append and update queries to get any updates and information other workers have entered on different inmates.
    if the FE/BE model is
    one datastore serves all clients
    you can have multiple FE's (ideally one per workstation) all talking to the same backend.

    form what you say it sounds like each person has their own back end whicvh you then periodically resync with the master backend.


    if you want to keep local copies of data then you need to be a bit smarter about how you do your update process or resyncing.

    form what you have said so far I think bound / unbound forms are a red herring in this case
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2012
    Posts
    20
    Quote Originally Posted by healdem View Post
    the problem to me sounds like there are multiple backends


    if the FE/BE model is
    one datastore serves all clients
    you can have multiple FE's (ideally one per workstation) all talking to the same backend.

    form what you say it sounds like each person has their own back end whicvh you then periodically resync with the master backend.


    if you want to keep local copies of data then you need to be a bit smarter about how you do your update process or resyncing.

    form what you have said so far I think bound / unbound forms are a red herring in this case
    Sorry if I confused you about the FE/BE issue. There is actually just one BE which is stored on the Shared drive state network. Then, each of us workers have a copy of the FE on our computers which we are entering data into daily. We will then each sync our computers with the BE every two weeks to get all updated information on each inmate such as transfers or information others have input on them. So essentially, we have 8 FE's talking to one master BE.

    The problem is, once I add someone to the table to insert data into, and then go back and update him, his information gets overwritten because on person #2, that updated information is blank on their FE.

    Does that make any sense? Sorry if it doesn't, this is the first time I have dealt with Access since one class on it in College but it was not this complicated. We barely scratched the surface of it's capabilities...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry thats not how the FE/BE model works
    FE contains only the user interface (forms, reports etc, and if required some config data that isnt' especially volatile if you so wish)
    the BE contains only data and perhaps some queries

    if you are entering data daily into your FE and then updating the centreal dm periodically then you don't have a FE/BE, or at least not my understanding of FE/BE in Access.


    I suspect your current problem, is that you don't know who has the most recent data, and you are runnign update queries which are overwriting others data.

    unless you need local data you should only have on datastore. if you have people out working in the field and need their own data thats fine. but you need to design your application so you know what data needs tobe updated, and what doesn't

    what I've done in the past is to write a SQL statement to a log file every toime soemone tinkers with the data if you capture the date and time of event you can readily handle the synchronisation issue
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2012
    Posts
    20
    Quote Originally Posted by healdem View Post
    sorry thats not how the FE/BE model works
    FE contains only the user interface (forms, reports etc, and if required some config data that isnt' especially volatile if you so wish)
    the BE contains only data and perhaps some queries

    if you are entering data daily into your FE and then updating the centreal dm periodically then you don't have a FE/BE, or at least not my understanding of FE/BE in Access.


    I suspect your current problem, is that you don't know who has the most recent data, and you are runnign update queries which are overwriting others data.

    unless you need local data you should only have on datastore. if you have people out working in the field and need their own data thats fine. but you need to design your application so you know what data needs tobe updated, and what doesn't

    what I've done in the past is to write a SQL statement to a log file every toime soemone tinkers with the data if you capture the date and time of event you can readily handle the synchronisation issue
    I think I am having a hard time understanding what exactly you mean, mostly because I am still getting used to what these different terms mean. Any way to idiot proof it for me?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Front End contains only the user interface (any forms, reports), and optionally some queries and or static data (eg basic cinfiguratiuon stuf that doesn't change or doiesn't change much)
    Back End contains the data (the tables) and optionally queries

    if your users are entering data into a local copy of the data and then peridodically uploading data to a central datastore (file on the server) then you are not using a frotn end / back split.
    Google

    from what you have said so far I think you are running local copies of the data and then periodically updating the central datastore

    there's an easy check when you open your front end are there any tables in there that are not linked or attached to another database?
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2012
    Posts
    20
    Quote Originally Posted by healdem View Post
    from what you have said so far I think you are running local copies of the data and then periodically updating the central datastore

    there's an easy check when you open your front end are there any tables in there that are not linked or attached to another database?
    Yes, this is what we are doing. There are tables that are not linked to any other databases and we are running local copies of the data updating the central datastore.

    So how do I fix this to be constantly updated so as not to overwrite things? Thanks a ton for the help, it is starting to make sense.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    delete your local tables
    link the tables in the back end to your front end(s)

    but this model only works if you always have the back end available when you use the application. if you need to work away from that connection then you need to redesign the update process so it can handle the disparity between different local copies. do a google on replication if you need to go down this route
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2012
    Posts
    20
    Quote Originally Posted by healdem View Post
    delete your local tables
    link the tables in the back end to your front end(s)

    but this model only works if you always have the back end available when you use the application. if you need to work away from that connection then you need to redesign the update process so it can handle the disparity between different local copies. do a google on replication if you need to go down this route
    Ok, that makes sense. There are actually three tables that are connected. They are highlighted in the pic. Everything else is not attached to the back end.

    http://img.photobucket.com/albums/v6.../Tablehelp.jpg

  14. #14
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    Here is a sample of what I am using.

    I have a network drive where my DB is located;
    Network Structure picture by pjhaction - Photobucket

    Inside the backend folder is the Frontend *mine is MSIS (everything except tables) and backend *mine is MSIS_BE (Tables)
    DB picture by pjhaction - Photobucket

    This is how it works
    - The user clicks the "MTOC Student Information System" Visual Basic Script.
    -- This will enter the Backend folder, then make a copy of the MSIS Frontend
    -- The copy of the Frontend will execute, leaving the original frontend untouched
    - The user has their own Interface, you can have multiple users have the same form open while data is inputted. (I put a refresh button on the form so they can see what the other users have inputted)

    Here is the link to clone: http://www.dbforums.com/6274786-post19.html
    -Note: I modified my code to do two things
    1. When it clones, add a 1 the the database title
    2. On the Splash Screen, VBA in the background checks for any temp db's that are older then 48 hours, if so then it deletes them. (keeping my network drive clean).

    Testimony: I have up to 30 people hammering away on data inputs and reports everyday with no data failures (over 23,000 student records).

    Let me know what you need next.

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

  15. #15
    Join Date
    Apr 2012
    Posts
    20
    Quote Originally Posted by PJHAction View Post
    Here is a sample of what I am using.

    I have a network drive where my DB is located;
    Network Structure picture by pjhaction - Photobucket

    Inside the backend folder is the Frontend *mine is MSIS (everything except tables) and backend *mine is MSIS_BE (Tables)
    DB picture by pjhaction - Photobucket

    This is how it works
    - The user clicks the "MTOC Student Information System" Visual Basic Script.
    -- This will enter the Backend folder, then make a copy of the MSIS Frontend
    -- The copy of the Frontend will execute, leaving the original frontend untouched
    - The user has their own Interface, you can have multiple users have the same form open while data is inputted. (I put a refresh button on the form so they can see what the other users have inputted)

    Here is the link to clone: http://www.dbforums.com/6274786-post19.html
    -Note: I modified my code to do two things
    1. When it clones, add a 1 the the database title
    2. On the Splash Screen, VBA in the background checks for any temp db's that are older then 48 hours, if so then it deletes them. (keeping my network drive clean).

    Testimony: I have up to 30 people hammering away on data inputs and reports everyday with no data failures (over 23,000 student records).

    Let me know what you need next.

    Peter
    Ok, thanks. I will have to take some more time to dissect this tomorrow. But I am not sure if I can do that without completely rewriting the DB. I could be way off though. I have the tables, and the queries set up, it may just take some tweaking to get things in the correct place so people can use it the same way.

    Thanks a ton for the info. I will get back tomorrow after reading more about your post and let you know if I need anything.

Posting Permissions

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