Multi User Access Database 1

[arun338]

Hello.....probably you have seen a lot of questions on this subject. I went through all these questions and still I am not clear. I have an Access database where atleast 12 - 15 people are entering data and 6 - 8 people viewing them(they don't make any change in the data...they just view the sorted data through queries). I don't think two people will be editing the same record due to the nature of the data (Both during data entry and data mining).

I have this nice database developed and working fine. Now my problem is making it multi user. My first reaction is to create one backend and numerous frontends to all the people who are using them and connect the BE with FE using Linked Tables. Is this the right approach?

1.Can I just make one FE and is there a way that more than one guy can access that FE.
2. What about bloating?
3. Will I loose speed with the numerous FE method
4. Should I write any VB code....like connection object
5. I guess my basic question is what is the best possible way to do this?

I would greatly appreciate any help. I am leaving this job in 3 weeks and I would like to finish the database before that

Thanks to all database gurus in advance

Arun

 

[ti976]

My background: AC97 app developer - important since AC97 is different enough from newer versions (like DAO vs ADO) where what I say may not apply.

1. Yes,yes; if you make a FE typically you install it locally on the user. You can make just one FE that every one can use but unless there are other concerns (like server security, data organization) there's not much point in that. Data viewers should not have a problem. Data entry may be a problem for multi user if two + people are trying to enter info on the same table a the same time - rare event, but it can occur.
2. Not much you can do but compact the db at regular intervals - you can automate the process on the server side. only other suggestion: keep deletes to a minimum.
3. Yes, whether it's noticiable is dependant on many factors. Factors include 'distance' - the further away the BE from the FE the slower, amount of data being returned, complexity of queries. If you use AC97, you'll notice.
4. No. If you can live with the speed. From what you have written, the only vb that you have to probably write is to write a trappable routine for record locking event when data is being enterd by 2 people at the same time - as mentioned in 1. In my experience, if you want speed, vb can significantly improve speed for certain scenarios.
5. if access can handle the load running as a single instance - I would leave it alone. 3 weeks may not be enough time to separate and debug everything before you leave.

good luck

 

[arun338]

Thanks a lot ti976.
I am using access 2000.

So, You would suggest to have numerous FE for every user.

The FE and BE will be in the same folder of a shared drive.

What do you think about the article below

http://www.aspfree.com/c/a/Microsof...ess-Application-for-a-Multi-user-Environment/

I guess I am too naive about the whole issue.

Could you please tell me what are the steps and what are the concerns while transferring a database into multi user. I am talking about the basic underlying concepts. Iam finding 100 different approaches in the net and I am confused. Please explain it in plain language. What do you meanby server side programing...i just have a BE and FE. Should I worry about Microsoft JET engines and all those stuff.

My apologies for not coming up with a specific question. If I can see the principle or the concept of this whole multiuser issue, I can develop the databse easily.

Once again, Thanks a lot for your appropriate and immediate response

Arun

 

[ti976]

Good article. More detailed and elegant than what I wrote.
Let me see if I can break it down.

starting assumptions:
1. you have a server or a machine that serves as a centralized file repository - I'll just call a 'server'.
2. other pc's that are connected to the server.
3. every one has the same security rights to access the server
4. your db is on you local machine and you are the only one to use it

"worry about Microsoft JET" ? No.

"server side programing"? I think you misunderstood me. It was only meant to suggest bloating problems solution. At this moment, don't need to worry about it.

"one backend and numerous frontends to all the people who are using them and connect the BE with FE using Linked Tables. Is this the right approach?"
Yes! or at least one of the right approach.

Here is an easy way of doing things.
1. put the database on the server - I'll call the BE.
2. create an empty database - I'll call the FE.
3. import all the forms,querys,macros,etc. EXCEPT the tables into the empty db.
4. create link tables from the FE to the tables of the BE
5. place a copy of the FE on to the pc of a user. Every that needs to 'use' the data will need a copy of the FE.

this file (FE) should run.
Most of your work is done - you have already created the data part of the db, & the user interface to the db. You are now ready to separate the two from each other.