Using Access on a Network

[dpimental]

All, I am working for a client to build a db application that will reside on a network. It will only have a handful of users (no more than 6 - 12).

It will serve to provide a data entry interface, reporting and analysis. As long as I do not set the db to open in exlusive mode, do you see any issues in having everyone use the same db as frontend / backend?

Also, since the company has its own security protocols, I am just looking up their username and using that to grant limited or full access to the system.

Your Thought?

David Pimental
(US, Oh)

 

[Remou]

I think you must split to front-end / back-end with that many users, especially as the database will allow data-entry.

 

[dpimental]

In the options of the database, it allows to be opened in shared or exclusive mode as well as using record level locking.

That seems to suggest that it was built to work on a network in a "shared" user environment.

David Pimental
(US, Oh)

 

[Remou]

An Access database can be shared in the way you wish, however, 'best practice' is to split the database and share only the back-end:

http://msdn2.microsoft.com/en-us/library/aa167840(office.11).aspx

 

[dpimental]

Got it. Thanks. I was trying to avoid that; but I guess yu're right. I know that I was trying to avoid linking tables and then relinking them on start up.

But, if that is what I need to do, then I'll do it.

David Pimental
(US, Oh)

 

[fneily]

If you're talking INTRAnet, I created a database for a local middle school to track students classes, behavior, attendance, etc. placed it on a shared drive and for the past 3 years the 12 people who access it have had no problems. So, you don't really have to split it.

 

[dpimental]

Thanks. That's helpful. I may split it up at a later date (in Phase II of the project).

David Pimental
(US, Oh)

 

[Remou]

Not splitting the database can lead to corruption in some cases:

http://www.granite.ab.ca/access/corruption/causessoftware.htm

 

[JoeAtWork]

I'm seconding Remou on this. Splitting the database is simply a no-brainer decision. There are absolutely no advantages to keeping the database whole, while not splitting almost always leads to data corruption (it's a matter of when, not if).

There's also the matter of maintenance. What if you want to add new features to the UI? This is very easy to do with a split database (you just update the front-end and redistribute to the users). On a whole database it becomes much harder.

In any case, working with a split database is practically the same as working with a whole one. You don't need to relink it every time the user opens it, just relink whenever you have finished making your changes, and distribute the front-end to your users.

 

[tviman]

I'm in 100% agreement with Remouo and Joe. The last thing you need is the headache/heartache of trying to fix a corrupted DB. And it's just too easy to split the database and virtually eliminate this problem.

 

[dpimental]

Thanks for the input guys.

I agree it makes more sense to split the db than not.



David Pimental
(US, Oh)

 

[fneily]

Wow. I guess the school's database and me have just been lucky for three years! I hope its not reading this thread.

 

[dpimental]

All, thanks for all the valuable input. It has been very helpful. One last question.

I have search for and found some vba sample code on the forum for relinking tables. It seems a bit lengthy and complicated. Is there any code that is simpler. I mean the tables I will be using will be a closed set. I won't be adding to them.

David Pimental
(US, Oh)

 

[Remou]

a few notes can be found here:

http://www.tek-tips.com/viewthread.cfm?qid=1401015

 

[dpimental]

Sorry, I responded to the wrong post.

Thanks Remou. I will take a look.

I might put the table list in an array, rather than another table. I'll see which is more optimum to me. Thanks again.
David Pimental



David Pimental
(US, Oh)