Access 2007 Sharing a DB with multiple users

[PCRIDE]

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!

[PCRIDE]

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?

[PCRIDE]

OH could it be the short cut?


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


I remember this...

[boblarson]

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.

[PCRIDE]

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?

[boblarson]

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.

[PCRIDE]

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?

[boblarson]

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.

[PCRIDE]

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.

[boblarson]

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?