Access on a LAN

[sorted]

Before I began the design on a Database I checked with network technicians to see that it would be okay to put the entire database on the network server. There were to be 3 users who would be accessing the database out of around 300 logged onto the network. These were my questions:

Will we be allowed access to Microsoft Access on the necessary machines?
Will we be able to load the finished database onto the main server?
Will we be able to create desktop icons on our machines to open the database?
Will we be able to import/export data to all other Microsoft Office software on the network?
Will we be able to create a file to store photographs of individuals and link to this file from the database?
Will the database, when loaded will be backed up during the course of the existing system backup?
I was told that all these things would be fine as long as there were no more than 7 Users, allowing any more than 7 users could lead to "Data Corruption"
Problem is that in the near future use will increase to up to 10 users.
I am due to speak to the guy after the weekend and I wondered if the issue could lie with "record locking". I intend to use "Edited Record Locking" for the whole database and the forms and reports through their objects property sheets.
Am I on the right track for a solution or am I missing some network issues which are beyond my knowledge. Any advice would be greatly appreciated as the chance to do some (By my standards) gutsy database writing will be lost over a networking issue.
Many thanks for any help
Regards
John

 

[The_Doc_Man]

If you had asked me about my network, here are the answers you would have gotten as they apply to my site. I think these answers are not out of line for MOST sites unless you have some mini-Napoleons running your network and/or file servers.

1. Will we be allowed access to Microsoft Access on the necessary machines? You or your project manager would have to obtain Access for all affected users. Network guys don't supply software. On the server, you will access FILES. Unless you are running a CITRIX variant, which you did not mention. And no, I do not recommend CITRIX most of the time because of the size of server you need to maintain operations.

2. Will we be able to load the finished database onto the main server? That is not usually a problem. The more important aspect of this problem is to choose a folder structure conducive to reasonable security and performance. I would have suggested setting aside a relatively high-level folder on the networked drive. Like, if it is drive S:, then S:\SortedDB would have been the path to the folder with the DB. Yep, top-level folder. That way you minize path traversal (not a cheap operation) and maximize specificity of security tokens. (Having too many projects share a common path makes security token evaluation an expensive operation, too.) It is easier to manage, really, and for a shared drive, having shorter "directory trees" is not really a problem unless you have a lazy administrator who doesn't realize that short trees are easier.

3. Will we be able to create desktop icons on our machines to open the database? This is not a difficult operation. If you can read (file-scan) the folder, you can create an icon into it for any purpose allowed by the folder permission set.

4. Will we be able to import/export data to all other Microsoft Office software on the network? Usually, Office is NOT on the network. The licenses for same require you to have individual licenses. Even if you have a site license (not unheard-of), you need individual installation of the Office package on all potential user machines. Has to do with registry issues. But a file is a file is a file. Export and import work on files regardless of where they are.

5. Will we be able to create a file to store photographs of individuals and link to this file from the database? File storage issue. If your sys admin guys have that much space on your file server or can plan for obtaining it, there is usually no reason to not do so. My advice in that case would be to make the photos reside in a sub-folder of your primary DB folder. Then you can build pointers inside the db so that when you open a form/report with that photo, all you have to do is a very fast bit of code in the Form-Current event routine to load the file spec into the .picture property of the image control set aside for the photo. NOTE: It would be extremely helpful if you could measure the size of typical photos and project the total size of the photo portion of the db.

6. Will the database, when loaded will be backed up during the course of the existing system backup? Usually, it is MORE work to EXCLUDE a folder than it is to just do a whole-disk backup. So if other parts of that disk are already backed up, it should be no biggie.

7. They told you 'allowing any more than 7 users could lead to "Data Corruption" ' - which is poppycock. What leads to data corruption is abuse of proper methodology. If you crack the whip over your users to always close what they open before they log off for the day and turn off their workstations, you will not have nearly as serious a problem. Unless the network guys are telling you that they don't trust their server or other parts of their network infrastructure. I can't address that if true, but you can certainly ask the question, since they brought up the limit in the first place.

8. Problem is that in the near future use will increase to up to 10 users. Access can handle a lot more than that. The question is how many will be online at once, and how many will be updating in that context? That is what governs the operations in question. If most of the time is "think time" i.e. look at the form for a minute or so, make a minor edit, save it, step to the next form, ... that is no problem. Continuous queries or updates that never stop - BIG problem. Sit and think about actions before peforming them - NO problem at all.

I don't see any serious problem with what you have described. Particularly if you DO NOT attempt to keep the picture in any record in the DB. Instead, keep the picture in a separate external file. Do not allow Access to embed the picture in the image control under ANY circumstances. Then, set aside a regular time to do maintenance like repair and compression. Do it JUST BEFORE your scheduled full backup, if you have a backup schedule that includes mixed full/incremental backups.

 

[Pat Hartman]

I would like to add one more thing to Doc's excellent answer. Split the database into a front end that contains forms, reports, etc. and a back end that contains ONLY tables. You can decide whether to leave the fe on the server or have it copied locally depending on response time.

Splitting the db goes a long way toward preventing corruption problems. The reason being that the be is only open for extremely short periods of time while data is actually being accessed. Therefore it is much less likely to be active if something bad happens to the network or one of the users. The fe won't get corrupted because the users never modify it. Corruption usually occurs when one user is changing something and another user or the network, improperly shuts down.

The other big advantage to splitting the db is maintenance. You can work with the fe offline and when all the changes are made and tested, kick the users out of the fe only long enough to replace it on the server. No need to worry about the data since it is in a separate database unless you actually need to modify table structures.

 

[sorted]

Thanks

Thank you both so much for your extremely informative and helpful replies. I feel a lot happier in what I am doing now.

Best Regards
John

 

[The_Doc_Man]

Pat's absolutely right. A split DB minimizes traffic because everything is local except the data tables.

I don't always think in those terms because our manager wants everything in one place and I don't get to split things I want to split. Yes, he has dark, pointy tufts of hair on either side of a balding head. And if you are trying to figure out where I fit in that situation, image Wally with a mustache and a fringe of hair instead of being completely bald.

HINT: If you don't get the context, look up DILBERT

 

[Crilen007]

Heh, Doc, you are on an access forum.

I'm sure all of us computer people know what Dilbert is.. rofl.