Network database

[deejay_totoro]

Hello all,

I really appreciate your help and advice (again !

I have made a few small databases for my office. They are not large things - but efficient enough and doing the job well.

Now, my office is part of a very large network of computers. I dont have "admin" rights, so all my databases are made on my machine, and then put on a network drive. A few users (around 6) access the database when they need to - and happily update and print reports etc...

But, this is just a normal "database" - not using SQL or anything like that. So, each user checks to see if anyone is using the database before they use it.

So, my questions are these:

1: Is it ok for a database to be "shared" under these conditions? Its only a matter of time before 2 (or more) people try to update the same record (or field) at the same time. I notice that Access creates the *.ldb file when any number of users are logged on. Is it ok to rely on this? Or... is it time to learn about SQL?

2: If I do need to start learning and developing multiuser (SQL) network based databases, where should I start? What kind of permissions should I ask the admin guys for? (There is already an IT development section - I doubt if they would give me access to their SQL server anyway...)

3: One other away around such issues: I was thinking about installing the MS Desktop SQL Server that comes with Office XP. I could get an unused computer and install it on that. That way, I could have a "mini" multiuser database that would require any IT supports help. (But would still give me the benefit of a multiuser database.)

I also thought about permissions. Using the built-in security wizard. But I also read that this can mess up any systems where other database securities are setup.

As you can see, Im pretty confused as to what is the best way to go from here.

Id really appreciate your help and advice.

Cheers!

dj_T

 

[The_Doc_Man]

Only you can determine the intended scope of your database. I would strongly advocate that you read up on Workgroups and the security associated with same.

A group of perhaps 20-30 users, any 5-8 of whom could be online at once, could work just great - or could bring your network to its knees in a heartbeat. It all depends on how you attack the problem. You can never ever any time totally stop a problem in which user A and user B both try to update the same record at the same time - but you can at least moderate the negative effects thereof, depending on how you set up permissions and locking policy.

Workgroups will allow that setup to happen more cleanly than trying to roll your own security and hope that it doesn't stink up the place too badly. If you set up workgroup security, make sure your individual users do NOT have the right to "Connect Exclusive" to the database. Make sure that the workgroup file is NOT the default system file.

There are other things you can do to further secure a database in a way that prevents meddling from folks who don't want to play the game correctly. Search this forum for articles about 1 year old regarding Securing a Workgroup. I have personally posted a few guidelines on that subject including a way to totally frustrate folks who forget to join the workgroup before entering the DB.

Adding SQL Server to the mix is a good, cheap way to better control the behavior of the database and to increase the potential number of simultaneous users beyond the practical limit imposed by Access and workgroups. It is, however, a compromise solution. ORACLE or other packages are better. Still, SQL Server does work correctly as far as I know.

SQL Server (or any other client/server DB setup) would also act to limit the amount of data transmitted over your network, which will make your network gurus muchly happier. This is terribly important if you have a network that is approaching saturation.

If you take the client/server approach, don't use your oldest, crankiest machine for the server. You will want some real throughput for that box. If you have a system with a newer disk that spins faster than 3600 rpm (5400, 7200, and 10K come to mind as newer speeds, 7200 is becoming VERY common these days), you might want to consider that. Make sure the box has extra memory and don't put a sluggish CPU in it, either. In general, servers need to be zippy quick, replete with storage, and equipped with an intelligent network card (so you don't bog down the CPU to do network stuff.)

In closing, spend some time up front thinking about what you want to do before you commit to a given design. Ask more questions in this forum once you are ready to get opinions on some of your specific ideas.

 

[deejay_totoro]

workgroup

Thank you for you reply.

I had thought that using a workgroup would only be of use for permissions?

I wasnt aware that it could help me with sharing/accessing a database intended for multiple users? (other than assigning permissions.)

One reason I stayed away from it was because I had read some horror stories, for example: someone in a similar environment had setup their own little "mini workgroup permissions" and it had affected every single database. For instance, anytime a user tried to open any database - they were always asked for a pass word etc... this wouldnt work for me.

Maybe I am missing something? As I dont really have that much experience?

Thanks again.

dj_T

 

[Autoeng]

DJ:

The horror story you tell of is because they did not implement security / workgroups correctly. Read the MS Security Faq's (several times)...http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp and many find this file helpful...http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

Implementing security / workgroups is not difficult but must be done correctly or you can secure every db, lock yourself out of every db or create what you think are secured apps but in reality are not. Fear noth though because if you will read the info I linked and ask questions here we will help you out.

 

[deejay_totoro]

Will this resolve....?

Thank you for your reply.

One little question: If I use the Access Security feature, how will this help my problem of multiple users, using the same database, at the same time?

Will the security allow the database to be successfully used simultaneously?

Thanks again!

dj_T

(Now I will study those links! cheers.)

 

[Autoeng]

You have to set sharing and record locking under the tools menu.

Tools - Options - Advanced - Default Open Mode - Shared

Tools - Options - Advanced - Default Record Locking - Edited Record

If you set these up you can use a db in a multi-user environ. Security and workgroups keep your users from changing things in the db.