Access 2007 sharing on local network (1 Viewer)

[chris89]

Hello everyone!

Please tell me what is the best way to share my access database on a small local network ?(3-4 users)

Thank you in advance

 

[jleach]

Hi,

Split the db, put the backend on the server. Link the frontends to the BE, then give each user a separate copy of the frontend.

This is the model design for database distribution over a local network.

hth

 

[chris89]

Thank you very much for your fast reply!

When I split the database do I have to use sql server , mysql or just a shared location where the back end would be located?

 

[jleach]

SQL Server is a complete separate product than Access (although the two can, and often, work together), and MySQL is a web-based database. So, no, you need neither... Access has it's own databse engine (ACE, formerly JET) which serves more or less the same purpose that SQLS and MySQL do (if in a much more lightweight fasion than either of those two).

So, put your Access Backend on the server share, and that's all you need to do in regards to setting up the data end of the DB. Then, link your tables to that Access BE from the frontends. (this is done automatically if you use the wizard).

It's very important to make sure that each user gets a separate copy of the FE though... under no circumstances should more than one person access the same FE file at once.

Stop back with any other questions.

Cheers,

 

[chris89]

... under no circumstances should more than one person access the same FE file at once.

So is there a way to restrict this?

 

[spikepl]

as jleach wrote ...

make sure that each user gets a separate copy of the FE

 

[chris89]

Respect guys!

 

[smaviddavid]

Does it matter if each FE is the same? but 4 copies on each desktop?

I assume your saying don't let people shortcut it from the network.

 

[jleach]

Does it matter if each FE is the same? but 4 copies on each desktop?

In most cases all the FEs are identical. The thing is, when one person opens a file, that session of Access does various things to make that session work. Now, if another person opens that same file, Access gets very confused, very quickly - in comes severe object and data corruption: a database developer's worst enemy.

(Note here that each FE links to the same BE... this is normal and accounted for, but in the same light, no user should actually open that BE file "manually" while there's any FE's connected to it).

I assume your saying don't let people shortcut it from the network.

Mainly that, yes, but there's other situations as well. A classic one is one file on a terminal server (citrix or the like) where multiple users log in and all access the same file. In this case, put four identical files on the terminal and tell each user to access their own copy.

Theoretically, you could put four files on the network share, and have each client put a shortcut to a different file, and you'd be fine. However, I wouldn't do that... might as well put it in their client machines.

hth

 

[chris89]

Is there a tutorial on how the procedure should be followed?

 

[spikepl]

Which procedure? There is a button that splits your db in fe/be and the rest is up to you

 

[jleach]

I'm not very familiar with the '07 UI, but indeed there should be a Database Splitter wizard.

Additionally, you'll have to use the Linked Table Manager (or whatever they call it in '07) to update the table links after you've placed the backend on the server. (each linked table has a property (Connect) that holds a connection string - in the Access case this is a the path of the backend file which it will connect to).

Many people actually write a procedure to handle this for them, linking either to the live BE database, or a different development copy of the database (it's never a good thing to be designing and testing while using a live copy of the BE).

You can go to http://mvps.org/access and have a look around there for this procedure (and while you're at it, look everywhere else there: this is the holy grail of Access websites).

cheers,

 

[chris89]

Ok who will be able to add data to the table?
I ve splitted the database set the BE database on shared folder and now opened it from the client!
But now I can't add data to the tables just view already added...
On the other hand when I enter data from the FE on the server side the tables get updated.
Again thank for your replies

 

[jleach]

When the FE is linked to the BE, you really shouldn't notice any difference as far as functionality goes... assuming those tables are correctly linked. Were you able to find the Linked Table Manager and check the path from the client FE to make sure they point to the server BE?

Also, make sure you don't have the BE opened anywhere else. If you're opened in exclusive mode, the FEs won't be able to connect to it.

Note that you won't be able to make design changes to linked tables... for that, you need to update the backend tables, then re-link to them from the FE.

 

[smaviddavid]

Ahh , makes everything much clearer now.

 

[chris89]

Ok found out what the problem was! I hadn 't set up the sharing and security on the server side to allow network users to change my files .
I also encountered very slow performance (Windows 7 on client - Windows Xp on server).
Now it is a bit better...

 

[chris89]

Is this slow performance normal?

 

[jleach]

Is this slow performance normal?

Tough to say. Developing for performance is a rather encompassing practice... not just one thing you can do, but many, and performance is directly related to the bulk of your application design.

That said, you need to realize that every call to your data has to travel over the network now... which is pretty much always a *lot* slower than a local disk fetch (it still needs the disk fetch off the server as well). So every time you look at a piece of data, it's coming over the network to get to you.

With good practices during design, you can still make performance fast... but it'll take a lot more typing than what I'm up for here in this thread to go over it. Luckily, you can google something like "MS Access Performace" and get loads and loads of results.

Cheers,

 

[chris89]

Thanks for replies!