Solved

Access DB on the network

Posted on 2004-08-25
19
292 Views
Last Modified: 2008-07-03
i'm putting my access database on the nwtwork, where multiple ppl will be inputting data into the same tables. Are there any issues I need to be aware of?
0
Comment
Question by:thinker388
  • 6
  • 6
  • 3
  • +2
19 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 11892857
Oh ... one or two <g>.

Seriously, mvoing from a single user environment to a multiuser one can be difficult:

1) Is this a split application (i.e. frontend/backend)? If not, do this before you deploy this application (there's a "splitter wizard" in most versions of Access, available from the Tools menu).

2) Do all users have their OWN copy of the frontend (users should share the data tables, but NOT the frontend)

3) How reliable is your network? Network glitches are probably the single most common cause of Access db corruption, so make SURE your network is in good working order.

4) have you taken care of the big 3: (a)Open a persistent connection to the backend db from every frontend, (b) turn OFF Name Autocorrect in the frontend (Tools - Options - General tab) and (c) set the "subdatasheet" setting in your tables to "[None]" (and, if you're using this, just don't ... I know you like it ... just don't do it).

5) Depending on the transactional nature of your application, you may run into concurrency issues - two users attemting to edit the same record at the same time ... this may not be an issue with your application, of course, but read up on this when you have time

More info here:
http://www.granite.ab.ca/accsmstr.htm
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11892924
you shouldn't have a problem if 5-25 people are using it, otherwise it will start getting slower.

but make sure there is enough disk space when you do a compact and repair (the same size as your mdb)
0
 

Author Comment

by:thinker388
ID: 11892950
1) well i think that both the front and backend are on the network. I mean I have tables, forms and reports and ALL of them r on the network.

2) Can u clarify that a bit further

3)Network is pretty reliable

4) a) how do i open a persistant connection? b) i went to tools>options>general but cant find Name AutoCorrect. i use access 97
c)what would turning subdatasheet off do?
0
 
LVL 84
ID: 11893079
Ahhh ... with A97 you don't have SubDatasheets OR NameAutoCorrect, so disregard those.

Do you have ONE database that contains ALL of your forms, reports, tables, queries, etc ... or do you have one db with all your forms, reports, queries, etc and ANOTHER datbase with your tables? If not, you really, really need to do this.

AFter splitting your database (the above paragraph), the you'll open a persistent connection by doing something as simple as this in a form that is ALWAYS open in your frontend:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM SomeTable")

Do this for a very small table (perhaps a lookup table) ... this forces your frontend to maintain a 'persistent' connection to the backend. Otherwise, you can run into issues where Access constantly drops/recreates your db connection, thus severely affecting performance.

And, regarding the number of users, jaffer is certainly correct, however I've seen applications fall over on their face with 5 users IF the db is not well designed. A lot of this depends on how well your data is strucutred, how well designed your forms (and I don't mean how "pretty" <g>). I've also seen applications with over 50 users that were working (and working pretty good)
0
 

Author Comment

by:thinker388
ID: 11893596
Yes I have ONE database that contains ALL of your forms, reports, tables, queries, etc...so this means I must do the splitting? what does the splitting do??

i have like a main form with 10 subforms..do u think it would hold up well aganist lets say 10-12 users?
0
 
LVL 84
ID: 11895431
Yes, you need to split the databae, and then make copies of the FrontEnd (the one with the forms, reports, etc) to distribute to your user. Place the "BackEnd" (the tables) on a shared machine - one that everyone can connect to (hint: when you run the Splitter Wizard, it will automatically relink your tables, but not correctly for your purposes - before passing out copies of your frontend, put your datatables on the server machine and, from the "master" frontend, click Tools - Linked TAble Manager and relink that copy to the new data table location). Then, pass out copies to all your users. If you've properly linked the frontend to the new backend location, and if everyone can "see" the backend, they should be able to start usingit.

I don't think I've ever built a form with more than 3 subforms, and that was a very, very limited use form (it was basically a setup form that is accessed very infrequently). IMO, 10 subforms is a kiss of death, performance-wise, in a multiuser application. The problem is that for EVERY recordchange (i.e. everytime a user moves from one record to the next), Access must requery 10+ tables to display this information ... and, each one of those subforms locks a different table (most likely) therefore you will end up with a LOT of recordlocks.
0
 

Author Comment

by:thinker388
ID: 11895698
thanks for replying...

one more thing...why do i need to split the database?? can i do without it?

0
 
LVL 84
ID: 11898619
Well, you could put a copy of your database on a shared workstation, and when users need to use it they can use that workstation to enter data in the db ... kind of defeats the purpose a shared database, but I guess you could do it.

Well ... splitting enables you to share your database among multiple users. Otherwise, if you don't, then when UserA open their database and adds data, UserB and UserC won't see it - how could they, since EVERY user would have their own copy of the database and would be entering data in their own tables. If you split the tables and link EVERY user to the SAME tables (not a copy, mind you), then when UserA enters a new record, UserB and UserC would see those changes.

Also, splitting enables you to deploy updates MUCH more easily ... if you don't split your db, then when it's time to deploy a new database, you'll have to write routines that will (a) import the users' tables and (b) import any user preferences.

I really don't see how you can do without splitting your database, at least in a multiuser environment.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 125 total points
ID: 11898997
>why do i need to split the database??
-It will make your database work faster, as all the Forms and Reports are local on the PCs.
-It will be easier to change things in Forms or Reports or Queries, you do it with the server copy (which nobody uses), then send it to everyone, otherwise if it is not a split, you will ask everyone tp log off then you can fix it.
-Size will be smalled for daily backups, as you won't have to backup the Forma and Reports on a daily bases.
-Less work on the server, as only data will be sent to the users, otherwise Forms and Reports will be sent on every Form entry or viewing a Report.
-More advaced, and it will be easier to upgrade to SQL server later.

I am sure I missed too many other technical points here.

>can i do without it?
-Yes, just put it on the server in a place where everybody can see and run.
-you don't even need Access workgroup.

jaffer
0
 

Author Comment

by:thinker388
ID: 11899216
shared workstation? u mean like one machine connected to the network and where every user can come and update the db in turns.. ooh well yeah thats a possibility...but thats kinda impractical in my case..

"Otherwise, if you don't, then when UserA open their database and adds data, UserB and UserC won't see it - how could they, since EVERY user would have their own copy of the database and would be entering data in their own tables. If you split the tables and link EVERY user to the SAME tables (not a copy, mind you), then when UserA enters a new record, UserB and UserC would see those changes."
 - ok, i have the db on a shared network drive..and when i make a change in the db from my machineA...then when i go to some other machineB connected to the same shared network drive...i can see the changes made(while i was on machineA) in the db viewing from machine B..so is my scenario different than what ur explaining here?



0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11900149
>so is my scenario different than what ur explaining here?
No it is not, on a small scale.

The suggestion was on how to make the performance better, and ...

BUT, if you go back to your original question:
>i'm putting my access database on the nwtwork, where multiple ppl will be inputting data into the same tables. Are there any issues I need to be aware of?

Then you will realise what we were talking about.

jaffer
0
 

Assisted Solution

by:yeahbaby692001
yeahbaby692001 earned 125 total points
ID: 11900389
Hi Thinker,

Not sure whether this Question has been resolved or not however just a point I would like to make is that when splitting the database into a frontend / backend, consideration should be made on how you will keep your workstations updated of any version changes to the database frontend structure and also how you link your tables.

When deploying a similar scenario a few years ago with a dozen workstations, I decided that since the frontend of the DB was about 2mb in size that I would simply include it in the logon script for the network and thus everytime a user logged on their frontend copy was replaced with the latest, this works fine as long as your frontend doesnt get too big otherwise people will experience a delay in login.  Another way to prevent this issue with the newer Operating Systems like XP or 2000 is to create a new scheduled task on each of the workstations that runs a shared batch file when the Workstation starts that will perform the same update.

A mistake I made when doing my first database split was using the splitter on a computer that had the network share mapped as a drive letter as is will map the tables to the drive letter rather than \\SERVERNAME\SERVERSHARE  This proved to be a pain because it required every computer to have a mapped drive EG: E:\ on it or the frontend would crash because it could not find the drive letter.

I hope I havent clouded the issue but have successfully answered some other issues associated with deployment of a split access system.

Cheers

YeahBaby
0
 
LVL 84
ID: 11901386
>> shared workstation? u mean like one machine connected to the network and where every user can come and update the db in turns.. ooh well yeah
>> thats a possibility...but thats kinda impractical in my case..

Highly impractical, I'd say ... which is why it's better to split the db and place your tables on a shared drive/server, and distribute copies of your frontend to users.

>> ok, i have the db on a shared network drive..and when i make a change in the db from my machineA...then when i go to some other machineB connected to the >> same shared network drive...i can see the changes made(while i was on machineA) in the db viewing from machine B..so is my scenario different than what ur >> explaining here?

I'm not sure ... what we're trying to get you to do is split the database, as explained to you earlier. A different scenario is thei: You can place the ENTIRE database (all forms, tables, etc etc in one file) on a network share and allow users to run THAT database (more correctly, this would be an "application"), but you'll have no end of trouble with this. Record locking issues will be frequent and frustrating, performance will be abysmal with more than 2 or 3 users, and updates will be a logistical nightmare (especially if you have users on different floors or different buildings) .... and:

... last but not least, the chances for corruption in the scenario I describe above are very, very high. Users sharing the SAME objects will invariably result in corruption. If your frontend is separate from your backend, then all you do is distribute a new, fresh copy of the frontend. If your frontend is integrated with your backend ... well, then you get to go to the latest backup (you ARE backing up daily or more, aren't you) and recreate as much of the data as you can and then tell your users "Okay, everybody remember what you did for the last two days and re-enter all that data", at which point you'll most definitely NOT be the most loved person in that office <g>.

0
 

Author Comment

by:thinker388
ID: 11907165
ok once i split the db, how do i "distribute" the front end? does the front end reside on the user's hard disk?
0
 
LVL 84
ID: 11907522
Yes, the frontend would be given out to each user and they would "install" it on their workstation. If you've used no ActiveX controls, then the "install" is as simple as copying the frontend to a directory and, perhaps, providing a shortcut for it. If you want to use an installer, Google on "Inno Setup", a free and very nice installer - I used to use this installer before moving to Wise.
0
 

Author Comment

by:thinker388
ID: 11907556
ooh ok...yes i have no active x so i can just copy the fe then.

also, now r there going to record locking issues?? and is there any option in access to address that?

and thanks for all ur help..as u can prolly tell im a very novice access user...and ive been working on this access project for this month and now that im done...i just didnt want it to fail now when its in a multi user enviornment..so thanks again...appreciate ur help
0
 
LVL 4

Assisted Solution

by:willcode4coffee
willcode4coffee earned 125 total points
ID: 12184200
There are some pretty wild misunderstandings going on here in this thread. I would like to try to clear some of them up.

First of all, I think you will probably have gathered that running Access on a network as a database is not the best idea. The main reason for this is something that I haven't seen mentioned: There is no server component to Access.

What this means is that in order to do a select from a table, that entire table is copied to the local machine before any filtering can be done. After all, Access is running on the local machine, not the network share. So no matter how well you write your queries, you will always get poor performance because the entire table must be cached to run any query. There is no other way.

Although it is a good idea in theory to split your forms out from the rest of your database, in reality it makes almost zero difference. I know a lot of people will take issue with that, but if you think about how Access works, you will realize it is true. A form is a database record stored in the Access file's system tables. It represents a very small amount of data, so when it is copied to the local workstation to execute, it is a minute bit of bandwidth used just for the form objects.

Here is where splitting can make an apparent difference though. If your tables are linked instead of part of the same database, and your forms or stored in an MDB on your local drive, your application opens the form locally and then executes a query. As I pointed out before, this requires it to cache the entire table to the local drive. Even with linked tables. However, it will only cache what tables are needed to populate the form, so you may not have to copy them all at first. If you are working with only one or two large tables, you will not gain much from splitting it into two files.

If you don't split it out, Windows will copy the entire mdb file across the network just to open it. But performance will be very good after that is done because you will be working off the temporary copy on the local drive. Record locking cost is the same either way.

Concurrency issues are a problem with a shared front-end because each user is maintaining a local copy of the data tables on their workstation and these must be reconciled with what is on the server. But if the database is opened in shared mode, there is no difference between linked and unlinked tables. The reconciliation is identical.

There is a lot of voodoo out there about how to make Access more efficient on a network. It is all smoke and mirrors. The reality is that it cannot be made efficient without having some kind of server side application (such as ASP) doing the database work on the server. Access is a desktop db environment that does not scale to networks well.

My advice? If the whole .mdb file is less than 10 MB, just put it on a share folder and set the record locking to pessimistic and default to shared mode when opening. If it is dog slow, try creating a new db, import all of your forms, and then link all of your table to the .mdb on the server. But your gains will be very small.

Hope this helps clarify some of the misunderstandings.

M@
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now