Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Access 2007 Sharing a DB with multiple users

This is a discussion on Access 2007 Sharing a DB with multiple users within the Microsoft Access forums, part of the Question Forums category; I have a series of Databases connected together and multiple people can be logging data throughout the day. I then ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Access 2007 Sharing a DB with multiple users

    I have a series of Databases connected together and multiple people can be logging data throughout the day.

    I then harvest the data by using excel and a data connection to create a pivot table to pull the data into excel.

    The problem is when anyone has the data bases open I cannot query the data base with excel and the other way around, when I have the excel file open, no one can get into the data base.

    Since these data bases are connected by queries, they don't query if someone has the other data base open or I have my excel sheet connected to it.

    These are all on a server share windows 2003 server and I used the Server Manager to create the share.

    What do I need to do here? Is this an setting in Access or is it the share. I am the admin of the data base - do I need to put it into a certain mode before making it available to all users?

    Its a race, whoever gets it first has access to it, you can't even open the data base to veiw the data if someone has it open, it returns an error that its locked or can't read the table because its in use or something like that.

    The exact error is
    You cannot open a db that is already open by User "Admin" on machine xyz, try again when the DB is avialble.

    Thanks!
    Last edited by PCRIDE; Jul 17th, 2009 at 01:48 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sharing a DB with multiple users

    The simplest way to share a database is to put it on a shared network folder. Although this is the simplest method, it is also the most limited. Before you consider this method, all the following conditions should be met:

    No more than a few people are expected to use the database at the same time.
    No Memo fields are present in the database, or if they are, they will not be simultaneously updated by different users.
    Users do not need to customize the design of the database.


    What does this mean
    No Memo fields are present in the database, or if they are, they will not be simultaneously

    I have a bunch of these. Is that why?

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sharing a DB with multiple users

    OH could it be the short cut?


    On each user's computer, create a shortcut to the database file.


    I remember this...

  4. #4
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Access 2007 Sharing a DB with multiple users

    For multiple users you should have a split database (Frontend / Backend) with the backend on the network drive and a COPY of the FRONTEND on EACH user's machine. You do NOT have a shortcut to the same frontend file. That will only be like playing Russian Roulette with corruption.

    See here for more about split databases.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sharing a DB with multiple users

    Thanks again, that worked.

    What I did is also created a ACCDE data base and the users can access that file from the share.

    Would you recomend not going this way and just have a local copy on their machines? I want to prevent constantly telling them a new copy has been updated, this way I can keep a working copy in another location, change the DB as need then complile a ACCDE file out to the public share.

    Thoughts?

  6. #6
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Access 2007 Sharing a DB with multiple users

    Using a single ACCDE file is just as bad. You still need to put a copy on each user's computer. You could use something like my free frontend auto update enabling tool in order to do automatic updates. If you enable your frontend for auto updating using my tool, you can just work on a master copy (for design changes) and then you change the version number and then the next time the user opens his frontend, if the version number on his doesn't match the number in the tables on the backend, it will tell them that it is out of date and needs to close. It closes and then deletes the old file, copies the new file down from the server, and then automatically reopens for the user.

    You can find it here.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  7. #7
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sharing a DB with multiple users

    So what is the difference from users accessing the ACCDE file from a share (all users using the same one) vs each having one on their local machine?

  8. #8
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Access 2007 Sharing a DB with multiple users

    Quote Originally Posted by PCRIDE View Post
    So what is the difference from users accessing the ACCDE file from a share (all users using the same one) vs each having one on their local machine?
    Exactly what you have said. You have a single file in which if any user's network session while they are connected and doing work gets disrupted in any fashion (dropped/lost packets) and it could be extremely brief, you all of a sudden have a corrupt frontend which nobody can use. With everyone having a copy of the frontend, if a single users' frontend corrupts it will not affect other users.

    You might also read more about all of that here.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  9. #9
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sharing a DB with multiple users

    Since I have split the DB and created the ACCDE file we cannot import the data from excel into the Tables stored in the split DB. We get an error that "MS Access was unable to append the all the data to the table, 0 records deleted , 0 records lost due to key voilations, do you want to proceed anyway?"

    I hit yes it acts as it completes but the data is not there.

    Any ideas?

    This worked prior to the DB Split.

  10. #10
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Access 2007 Sharing a DB with multiple users

    What are you using to import? Code, macro, what? And what is the exact process? Also, are you trying to import to an existing table? If so, is that table linked and does it have a primary key field set up?
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com