Access 2007, LAN, Sharing

[AJordan]

I am still in my conversion process of updating all existing databases to Access 2007 and I am wondering if there is anything I should be concerned about in regard to the following.

1. I have a split database with the back end located on the "host",
2. The LAN consist of 4 computers
3. Each cpu has a copy of the front end installed on it's local hard drive via Access 2007 Runtime and Developers Extension
4. A majority of the database is used for producing canned reports and graphs
5. Within the backend "tbl1" is fixed and "tbl2" is updated (by adding records via data entry by a user).

Is there something that can go horribly wrong with this set-up? What happens when 2 users are entering data at the same time?

FYI. one network has XP and another has Vista (I will attempt this on the network with all XP first). Also is is not feasable that users entering data will enter the same record at the same time, so this is not a concern (could happen but it shouldnt)

Thanks in Advance!

 

[mcclunyboy]

Well I recently set up 1 database with multiple users cross platform - mainly XP with office 07users but with some Windows 2000 and office 97 users. All the 2007 users accessd via 1 access frontend on a network share, and all the 2000 users did the same with an access 97 front end.

I have never come across a problem with splitting the database and the "locking" files that are created when a user accesses the file limits what could go wrong. In my experience if 2 users enter data at the same time they just need to refresh the screen to gain the new information.

I know its not a technical answer but I haven't had any problems...touch wood

 

[DCrake]

Two or more users should NEVER open an application using the same front end be it on a shared drive or on a local machine. Each individual user should have their own private entrance. With their own entrance you can track who is logged in and on what machine, if they all go in through the same door you wont be able to track this and it will, and I mean will lead to corruption. Not only the front end but more importantly the back end where the data is stored.

David

 

[mcclunyboy]

In our most used database we use Windows Authentication and track the users but have never had a problem - this uses VB which is not my forte but works no problem - we do log events on this one.

Wait - we have plenty of databases saved on our network shared which are directly accessed via a front end on the network share...we save nothing to c:\ since they are not backed up... whilst I have worked in this position we have never had a problem with corruption - duplication occasionally when people copy the database to their home directory but never corruption - are you saying we have just got lucky?

 

[DCrake]

Its a bit like going to a football match.If you give everyone a ticket and tell them to go to a specific turnstile as this is the only entrance, come game time there will be congestion as everyone is trying to get in at the same time. However if each user was given their own seperate turnstile then everyone can come and go as they please knowing that they will not encounter any hold ups. Now maybe uptil now you have had a steady stream of people all queueing up one behind the other. But at some point there is going to be a mad rush to get in. This is when the trouble starts. Its not the data that initially get compromised its the UI. But at some stage you may, no will, get data issues.

This is the general consensis of opinion amongst the learned Access developers found on this and other forums. It's better to be safe than be sorry. I even split FE / BE when it is a single user system.

David

 

[mcclunyboy]

Just mentioned this to my boss -

At the minute they are of the opinion "if it works, dont touch it"....

My best suggestion was to provide all users with a folder in their Home Directory (which is backed up) containing the front end databases...were trying to avoid saving to their c:\ for obvious reasons

 

[DCrake]

Then that is fine, as long as each user opens up their own copy of the front end it does not matter where it is. Well that is not strictly true, if the front end is on a server then Access (which is installed on the client) will have to drag the whole application across the network, then go back and get the data. This can be a bit resource intensive. But if this does not compromise the network resources then it should be fine.

David

 

[mcclunyboy]

Ah ok - all Front Ends and the Back End would be on the same server but different shares...

 

[DCrake]

Yes that correct. Also this helps if you do a product update you can simple update the front end in each users home folder.

David

 

[AJordan]

LOL, looks like my thread got hijacked. But it looks like I should be fine. I was just asking if there were any quirks with 07 that I need to be aware of. I will begin the installs and updates this weekend. Thanks guys.