DB for client-server use

[jayke]

Hi all,

I created an application that I was using at my work. Now other people would like to use it. I would like to have a client-server setup for my DB.
Can I do this by just putting the application on a server? Can a single application handle multiple users?

What if I open a recordset and I keep te recordset open for 20 minutes..
In the meanwhile another user tries to adapt a record. Will this go wrong?

I have read some stuff about splitting the DB. But is this really necessary?
Once splitted I can never go back to the original.

What is the best thing that I can do.

I know security is also important but I just want to know what the best solution is for multiple users on one DB.

thanx

 

[pbaldy]

If you have multiple users, you really should split the db, with a front end on each user's PC and the back end on the server. You could switch back to a non-split db later if you wanted. You would be asking for corruption issues to have multiple users accessing the same file on the server.

 

[Pat Hartman]

Splitting a db is really quite easy and is the best solution for a shared database. Giving the users their own copy of the front end and leaving the back end with the data on the server allows the data to be shared with minimum jeopardy. Also having a separate fe makes it easier for you to manage changes. You can make changes to your own copy of the fe and then when they are ready for prime time, you can distribute the new copy of the fe without worrying about converting data.

Splitting the db also gives you some protection against a user accidentally changeing something and causing the db to break. If they break their own db, you can just give them a new copy. I rarely secure my db's. I prefer to just use the start up options to hide the database window and I distribute an .mde rather than an .mdb so the users can't mess with the forms/reports/code. If the data needs to be secured, I use network security and limit access to the directory that contains the db. If the client insists, I implement Jet security but don't recommend it unless your fe is actually propritary. In that case, you will need to use the built in security to lock it down.

Jet (the data access part of Access) manages concurrent users by createing an .ldb file. This means that users need sufficient network permissions to create new files in the same directory that holds the .mdb. In the situation you described, the second user would be allowed to save the data but if you changed it without refreshing the form, you would get an error message telling you that someone else had changed the data.

 

[jayke]

Hi,

Thanks for your responses.
So splitting the DB is the best thing to do. Ok, I have something about it and indeed it doesn't sound that spactacular.

But what about the .mde and .mdb files? Each users should have such a file to have access to the DB? Can someone tell me more about it?

I can use a splitted DB without them?

thx

 

[Pat Hartman]

To split the database, use the database splitter under tools/database utilities. After the db is split, move the back end to the shared directory on the server. Use the linked tables manager, also under tools/database utilities, to relink the be. Check the "always prompt" box. Then, rather than choosing a specific drive letter, enter the UNC name for the directory in the File Name box. The format will be something like - \\servername\path\yourdbname.mdb

Using this method rather than a specific drive letter will allow users to access the be without having a drive mapped or worrying about using the exact same drive letter as everyone else.

Once the db is split, you can "compile" the front end to create the .mde. You will need to fix any compile errors if there are any before Access will make the .mde.

Once the .mde is created, you can distribute it to the user's desktop. Be very careful to make a backup of the .mdb and store it in a SAFE place. There is NO WAY to convert an .mde back into an .mdb. The .mde will contain no code so no forms/reports/modules may be modified or added. If you need to make modifications, you need to modify the .mdb and then create a new .mde to distribute. YOU CANNOT CHANGE the design in an .mde.

 

[puthenveetil]

Hi Pat,

Can I rejoin a splitted be and fe to a single mdb??

thanks

 

[jayke]

Pat,

Thank you!
With the description you gave I can certenly move on!!!


Cheers!

 

[WayneRyan]

puthen,

Yes, you can rejoin them. Just import all of the front-ends objects (except
the linked tables) into the back end. Or vice-versa.

Wayne