Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2009
    Posts
    49

    Unanswered: can users access database at the same time? .mdb ms access 2003

    Hi
    I have a user who created a database using ms access 2003 the problem is, if he's opening the db and add data or made some changes, the other user can't add data or made some changes. but if he's exited the program, then the user can make some changes.

    i would like to know if its possible; that they can work at the same time when they open the database? its ms access 2003

    I've read some article from here: http://office.microsoft.com/en-us/ac...882971033.aspx

    does that mean I have to set the "No Lock" under "Default record locking" ?

    Thanks

    I attached a .jpg here to see the program:
    http://www.freeimagehosting.net/imag...ed11af4cc5.jpg

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I never let multiple users add data to tables from the same .mdb file, the record locks get in the way too much for me. The usual solution is to split into a FE/BE setup, but (as a warning) this takes a fair amount of time on your part and takes intermediate Access skills (and remember to ALWAYS backup your data before trying this kind of thing). If you can't do it, consider asking your IT department.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    49
    @nckdryr: thanks for the quick reply. that means the user should have, back-end like "SQL server"...

    the user mentioned this is for his personal use, so I'm not sure if he used a back-end. As I checked the database today, it only contains one table.

    Should I recommend that he shouldn't have a multiple users to access the database at the same time?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    the FE/BE split is simple; it is found in 'Tools' 'Database Utilities'

    no you do not need any special server type product.....just put it in any folder that everyone can get to using the normal MyComputer/MyNetwork....

    put an FE on everyone's desktop and link to the common BE using Link Table Manager...non of this requires DBA or programming skills...it is all embedded in the product - - you simply need to know where to find it....

  5. #5
    Join Date
    Feb 2009
    Posts
    49
    @NTC: where can I find a complete instructions for this? The database is by the way in ms access 2003.

  6. #6
    Join Date
    Feb 2009
    Posts
    49
    @NTC: if I search this in google, what's the correct term to use: is it:

    - splitting tables ms access 2003 (how to)
    - splitting database ms access 2003 (how to)

    thanks

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    oh you can stop by any big bookstore....or get used ones cheap from Amazon....a university bookstore too.... lots of text books of Access are out there and they all cover the basics of splitting the db....be sure to buy one in the version you have..... there is even an "Idiots Guide..." though it isn't particularly good in my humble opinion...

    you can google around for tutorials online probably too...not sure if they are any good or not though...

  8. #8
    Join Date
    Feb 2009
    Posts
    49
    thanks NTC

  9. #9
    Join Date
    Feb 2009
    Posts
    49
    hi again, i tried to do the split and now i'm getting an error:
    please see attached jpg. what does that mean?


    additional question: is it possible to split your database many times? i'm trying it first at home and tomorrow would like to try at the office. would that create any problem?

    thanks
    Attached Thumbnails Attached Thumbnails error1.JPG  
    Last edited by tintincute; 12-02-09 at 16:52.

  10. #10
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    From the screen shot (and the error) it looks like you've got a form open...to do a split everything (Forms, reports, tables, etc) must be shut down.

    Here's my method for splitting (at least while I'm testing my db speed):

    1) Back up the MDB file (at least once, I usually go for redundant backups to different directories just to be safe)
    2) Open Access
    3) Open the .MDB *only*, don't open any forms, tables, etc
    4) Split the DB using the wizard, it will prompt you for a new name and location for the FE and BE files (at least it does in Access 2K2)

    Once that's all done, then move the back end file to wherever it's going to sit, use the link manager to reconnect the FE to the BE (using the FE .mdb file), then test test test (never rely on the wizard doing everything exactly the way you'd think it should...)

    Sam, hth
    Good, fast, cheap...Pick 2.

  11. #11
    Join Date
    Feb 2009
    Posts
    49
    @SCrandall: yes the form was still open. so i closed it and tried to split without opening the forms and it was split successfully.

    my next question is: I moved the back-end to another, folder and then it didn't work again.

    what should i consider if i split the database?

    Thanks

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have one MDB which contains the data
    have a MDB which contains the front end (the forms, queries, reports etc..)
    each user should have their own copy of the front end on their own desktop/computer. ideally the front end should be deployed as an MDE so users cannto tinker with the front end. there is code int he codebank to help manage the front end deployment.

    the developer needs their own data and their own development front end

    the developer should make ceetrain that they keep adequate backups of the front end as they are working on it. and shoudl also keep the original MDB that generates the MDE.

    whenever you split a db you need to make certain you relink to the data back end
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Feb 2009
    Posts
    49
    @healdem: so that means after i split the db its not yet finished?
    relink- where can i do that? is it under the Tools> Database Utilities > Link Manager?

    is that correct?

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Tools->Database Utilities->Link Table Manager.

    Check the "Prompt for Location" checkbox, click the "Select All" button (to select all tables or check the appropriate tables), and then click OK.

    It will prompt you for the new location of the backend mdb file which has your tables and give you the "All tables have been refreshed" when complete.

    You should ALWAYS refresh your linked tables this way in your frontend whenever you make table design changes (although you don't need to check the "Prompt for Location" unless you moved the backend mdb.) Regardless, if it can't find the backend table, it will prompt you for the location anyway.

    If you make a lot of table design changes/relationships/indexes/etc..., I will sometimes delete and just relink the tables versus refreshing them.
    Last edited by pkstormy; 12-02-09 at 20:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Feb 2009
    Posts
    49
    hi thanks for all your suggestions. it seems i made it and i just would like to know now, the network drive so that i can store the back-end there and then test the front-end.

    So now, I have the back-end and the front-end, how will i distribute the front end? Can I just simply copy it to users PC?

    Can they save it anywhere in there PC?

    Thanks

    My additional question is: if the database is already split can different users have access on the database at the same time? like adding information and so on...?
    Last edited by tintincute; 12-03-09 at 05:58.

Posting Permissions

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