What are the issues of implementing an Access DB on Network

[davesaint]

I've just completed building an Access97 Database to be used to track and manage our project/s issues. Each user will have theri own copy of Access installed on their PC's. I plan on placing the DB out on the share (OS NT4) and setting up a link from our intranet to the DB. What are the possible issues I need to be aware of before I do this? How many users can simultaneously access the DB at the same time without the DB bogging down? (Network 10/100)What is the best way to implement this DB?

 

[Monguse]

I can't answer all the questions, but I can tell you this Access was not designed for use on a network and not the best DB to use. If like me, the Co. you are working for is too cheep to buy SQL (approx cost $1500 - $2000) it will work. "The beauty of the second amendment is, that it will not be needed until
they try to take it." - Thomas Jefferson

 

[shaunk]

I wouldn't recommend it. We have a differnet setup in that
we have the D/B setup on a shared diretory on a server which is part of a WAN.
The results have been appalling. Out of 13 sites where this arrangement exists, at least 9 have had serious issues and one is no longer usable as it crashes with "unrecognised databse format" every other day.
I suspect that infrastructure variables have a large part to play in that the network needs a serious upgrade as do the old NT PC's.
We have tried putting ACCESS on Citrix but it maxes out the Citrix server.
Our best success has come from putting the D/B on a local machine with a shared directory for local users.

 

[LarryDeLaruelle]

Dave:

Now that you've heard the bad news . . .

I have a number Access 97 and 2000 databases running on an NT 4 network.

The users have a mix of NT 4 workstation and Windows 2000 professional.

To date, I have not experienced any of the problems described in the other responses. My db's are stable and work well in the environment.

We don't have a large number of users hitting any of by db's at any one time -- probably no more than 6 at any given time so I can't say what would happen if you intend to have a high number of concurrent users. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[fpdkris]

I agree with Larry, I have a totally mixed environment with 95, 98, NT 4, and 2000 PC's using Access on a Local Area Network. My environment is Town Gov. and even though I have access to SQL Server, we use Access most since most applications do not require more than 30 active users and generally take a couple of years of input to reach 100 MB of actual data, which really is probably the info you were looking for. Scale of your application is crucial. As a rule, if the application will be used by more than 5 people, we split the application and place the front-end on a client PC, Data on the a Server share. Creating an MDE also helps performance. Please be-aware of two things, it is crucial for someone to manage your application, compacting it from time to time and occasionally even archiving data off the original application.

Lastly if your app will be used in a WAN, unless you have an extremely fast pipe T1, Fractional T at 512kb, or ATM it will be slow at the remote sites due to the amount of information required to be passed up and down the network. If 56Kb or ISDN is your connection expect to fall asleep at the client. For slow connections to remote sites (ISDN or weak Fractional Frame Relay) I really suggest using a package like PCAnywhere and have the remote client take control of a PC on your LAN, then only the screen/window mapping will be sent to the remote PC not all the data.

Hope that helps.

 

[davesaint]

The DB would be placed out on a local share. We have 50 people in our organization that would have access to the DB. I cannot imagine more than 5 users at any given time accessing the DB. I plan on placing a copy of the DB on the share today and running some tests (maybe have 5 - 6 users try to access the DB at the same time) to see what happens before I try to implement it. If the test is successful I plan on compacting the DB daily and to administer it closely.

 

[allanon2]

We have an access app with 400 users. Sounds a little unrealistic doesn't it. Well, the back end is actually SQL 7.0. It is running on NT 4.0. What we have done is created and mde. Then we put this mde on a network drive. Step number 3 was to set up a batch startup file that we used Novell to roll out with. This batch startup file copies the mde to the user machine every time the user enters the app. This gives us two main advantages. 1) Easy rollout of new version, 2) We never have to compact and repair the database. The one on the network is never actually used and the user version are overwritten ever time the user enter the app. We have had a few minor problems but it has been running six months now and has only gone down for a half day once and that was because a dba restored a empty database over our production one thinking it was test.

 

[shaunk]

These all sound like great suggestions as I am managing a galaxy of ACCESS D/B's without any real expertise at all.
I am continually running into problems, primarily because of the ancient PC's and slow Network connection.
Can you tell me what an MDE is and if it is an available option in ACCESS 97.

 

[LarryDeLaruelle]

Shaunk:

An MDE is a compiled version of an MDB. I don't have much experience with them but I understand that they have some advantages and some drawbacks.

Advantage: Operates faster.

Disadvantage: Difficult to maintain/manage.

Not much info but I hope it helps. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Monguse]

One issue I did not bring up when I first told you the Access was not the best choice to use. One of the problems no one has has said anything about is the simple fact that when a user access a table via a querry or what ever Access unlike SQL pulles the entire table accross the network which takes more time and loads the network with useless traffic, SQL in this case takes the querry and then sends only the information requested by the querry NOT the entire table. There are several other reason such as slow access times (with lots or users) poor record locking by Access.. It locks pages, which may have more than one record (depending on the record size) etc....

Yes it will work, but it is not even close to SQL in networking... "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.org