Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Multiple Users--Big Issue (Access 2007)

    Hello,

    A database I designed for use at work keeps having issues. It is designed in Access 2007, and it is on our network through a shared drive where everyone has access to it.

    I have read that access should be quite capable for up to 20 users simultaneously, but it seems like if there are 2 or 3 people in this at one time, someone somehow gets disconnected....and while they are adding records, they do not show up on the database, and if they close the dataabse that they have open, the records are lost. This happens almost everyday, and I'm about ready to pull my hair out.

    What can I do to make this database so that everyone can use it at the same time? The number of users here at the same time will never exceed 20.

    Thanks,
    Chris

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    Access can easily and routinely handle multiple users - and more than 20. But one must implement it as defined by MS. If you are administering a db of this size & importance be sure to invest in a textbook for the version you are using, as they are easily found on Amazon or any big box book store.

    The database must be split. The BE alone is on the shared drive. The FE is to sit on each user's desk top and be linked to the BE.

    Records never get lost. You have some sort of set up problem. Possibly you are linked to a test db or more likely people are using their own version of the db and you are not sharing a common BE file. But until it is set up as described above - there is no other trouble shooting needed - - first set it up as described and double check that everyone is linked to the same BE. At that point if there are still issues it would justify trouble shooting further.

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    by "desktop" one means on their PC - it doesn't literally have to be on the desktop location - it can be in any folder....

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by NTC View Post
    Access can easily and routinely handle multiple users - and more than 20. But one must implement it as defined by MS. If you are administering a db of this size & importance be sure to invest in a textbook for the version you are using, as they are easily found on Amazon or any big box book store.

    The database must be split. The BE alone is on the shared drive. The FE is to sit on each user's desk top and be linked to the BE.

    Records never get lost. You have some sort of set up problem. Possibly you are linked to a test db or more likely people are using their own version of the db and you are not sharing a common BE file. But until it is set up as described above - there is no other trouble shooting needed - - first set it up as described and double check that everyone is linked to the same BE. At that point if there are still issues it would justify trouble shooting further.

    I had actually thought of this, like having the tables on the shared drive and have the forms and queries on the local PC.....Problem is, I don't know how I would set up the recordsets to link to the other database? Do you happen to have any kind of example I could see and work with?

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This will solve multiple user issues (you can have 100s of users). I use it all the time. You'll never get another locked by another user error or any other problems with multiple users.

    http://www.dbforums.com/6274786-post19.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2009
    Posts
    340
    you have "thought of this" !!

    dude - that's sorta like saying you've thought of stopping at the red traffic light...

    no choice....that is the defined implementation of a multiuser environment for Access...

    you really need to get versed via a textbook - one should not be flying blind in user forums for a db supporting 20 people....

  7. #7
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pkstormy View Post
    This will solve multiple user issues (you can have 100s of users). I use it all the time. You'll never get another locked by another user error or any other problems with multiple users.

    http://www.dbforums.com/6274786-post19.html
    I can't seem to open this as a vbscript file....work must not have the proper program.

    Quote Originally Posted by NTC View Post
    you have "thought of this" !!

    dude - that's sorta like saying you've thought of stopping at the red traffic light...

    no choice....that is the defined implementation of a multiuser environment for Access...

    you really need to get versed via a textbook - one should not be flying blind in user forums for a db supporting 20 people....
    Yeah, I have access 2007 for dummies, and vba for dummies....just have a hard time finding time to read them Someday...

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    chris07tibgs
    did you get the problem sorted

    I have written a number of MSaccess program 1 program had over 50 user and never had you problem

    this is what I do

    split the Database

    Tables in 1
    eveything else in the other
    that
    I put the data(tables) on to the network in a hidden share $
    give everybody full control over that folder as it hidden on one can see it to delete
    ie \\servername\sharename$\data.mdb

    in the front end make shoure the table are link by the unc name \\servername\sharename$\data.mdb

    I create a folder on the local computers call screens
    now copy the front end to the local computer put shortcut on desktop job done

    there is 1 problem you will have with the DATA.mdb it will grow very big fast

    so
    what i do create a autoexec macro that close the database
    then on a machine that you know that run 24/7
    set that msaccess to compact on close
    now create schedule on the computer to run at 1:00am everyday to open the database ( tables)
    the autoexec will start closing the database then the compact will kick in
    and the database will stay nice the happy for u
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by chris07tibgs View Post
    ...Yeah, I have access 2007 for dummies, and vba for dummies....just have a hard time finding time to read them Someday...
    if you are being paid for this then get yourself (or your employer) some decent books

    Access for dummies is just that, get a good book such as Access Developer Handbook, and read it, the re read it, then if you have time re re read it.

    in the meant time bone up of front end / back end design
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The *.vbs cloning script is a script (like other vbs scripts) typical in most environments. Like *.php scripts and other scripts, you use NOTEPAD (as described in the code bank post) to edit the *.vbs script (ie. OPEN WITH - and then select Notepad).

    There's no 'special' type coding in the *.vbs script. It uses the same type of system command to copy/paste a file as the system uses for example if you were to copy/paste a file in Explorer. The only thing the *.vbs script does it adds the user's LOGIN name to the copied file (and it then launches it.)

    So for example, if you have an mdb named: MyCustomerDB.mdb. After running the *.vbs script, it will copy MyCustomerDB.mdb and add the user login name, paste the file with the new login name, and then launch MyCustomerDB<LoginName>.mdb (ie. MyCustomerDBPaulK.mdb) Thus, each person is essentially opening their OWN copy of the mdb file and you are no longer restricted by the limitation of X users in a SINGLE mdb file and you won't ever get a 'locked by another user error' again.

    The other nice thing is that if a user's cloned copy of the mdb becomes corrupt (ie. they lost connection), when they run the script, it clones from the 'safe' source mdb file again and the user's mdb file gets recreated (without any errors). - Thus, your source mdb file is ONLY used as a file to clone from and is never really opened. ALSO, a corrupted mdb file (for a user) doesn't EVER affect any OTHER users (since again, each user is in their OWN mdb copy, freshly cloned from the source when they ran the script.) - thus, you'll never need to hunt down everyone who's using the same mdb file and have them all close out so you can compact/repair a corrupt mdb file or if you need to copy new code.

    Then whenever you want to copy new mdb code, just copy the new code over the source mdb file. Users don't need to close out of their copy of the mdb file (unless you tell them to) and to see the new code changes, you simply tell them to close and reopen the mdb (using the *.vbs script again.) You don't ever have to worry about having all the user's closing out of the mdb to copy new code.

    In the folder where you designated LUName (that you edit/change in the script), you will then have multiple mdb files with names like MyCustomerDBBillH.mdb, MyCustomerDBJoeM.mdb, MyCustomerDBJillS.mdb. These are all the cloned files (DON'T have user's run these - they need to execute the *.vbs script EVERY time to open the mdb, otherwise they are not cloning from the source mdb for the latest changes.) - the vbs script will copy OVER (ie. re-create) the MyCustomerDBPaulk.mdb file. But you can look at this folder and see who's currently using the application (you'll see a MyCustomerDB<LoginName>.LDB file in the folder). And you can also see when the person LAST opened the mdb file (by looking at the creation date of MyCustomerDB<LoginName.mdb file). The only drawback (or plus) to this is that you will end up with multiple mdb files in the LUName folder with your user's login names attached. These can be deleted at any time since when a user launches the vbs script, they will be re-created again. But you can also use these cloned mdb files to see when users last used your application and if they are currently using your application. BUT your source mdb file again, stays safe and is never opened so you can copy new code at any time without making everyone close out of the application.

    But PLEASE read the code bank description for the vbs cloning instructions! It's a very simple script but you need to set it up correctly (ie. change the 1 line - LUName to point to your source mdb file) After that, just make sure users get in the habit of running the *.vbs script instead of opening the mdb file to launch your application. The *.vbs script will make your life much easier. It has for me (for many, many years.)
    Last edited by pkstormy; 01-01-10 at 12:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Oct 2009
    Posts
    204
    Ok I'm at home, not at work, and at home I work off of office 2003.....

    So I made two test databases, one with a table, and one which linked to the table and had a form. That worked properly. I then set up the vbscript from pkstormy and that again worked perfectly and as expected.

    My only question, since I am working off of Office 07 at work, .....is all I have to do differently in the vbscript file, change .mdb to .accdb? If this is the case, problem solved, and thanks so much to everyone for pointing me in the right direction!

    -Chris

  12. #12
    Join Date
    Oct 2009
    Posts
    340
    just for the sake of clarity to any newbie that may read this thread; a multi-user, split Access db is a standard implementation - totally embedded & implemented via the standard tools available in the product and requires no programming know-how nor any custom vb script whatsoever.

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    For the vb script, you must change the location of the file name which is LUNAME = "c:\myfolder\myfilename.accdb" for example. You can also utilize the UNC name.

    Then you need to change the line which copys the file (whatever type it is) (ie. change *.mdb to *.accdb.) You'll see comments in this section of the script. It will say change it to *.mde for compiled files but disregard that and change it to *.accdb (uncompiled) or *.accde (compiled). - whichever file type your working with. This is the code part which does the copying/paste (or replace) - adding on the username.

    The next code executes the cloned copy with the username added on.
    Last edited by pkstormy; 01-05-10 at 22:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Cloned copy

    I'm a little new to access. Where does the vba code for making the cloned copy go? Does it do in the globals module?
    Thanks.

Posting Permissions

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