Can anyone give me the pros and cons to having 10

[kat25]

Hey!
I'm trying to determine which would be the best solution for
deploying my database.
Currently, I have my database on a shared network. I have not split the database yet.
I am trying to understand what the pros and cons on having my clients access the database,the front end and back end, when split, to both be located on the server, or having the tables stored on the server and then having the forms stored on each individual's local pc? One of my coworkers strongly recommended that I should not put the application (forms) part of the db on each pc. However, I really thought it would increase the access time for each user if they only had to go to the server to get the table data.
I have approximately 15 users in various states that will need to use the db. Although my clients are located in several different states, I travel to these locations often.

Any suggestions on the best method of distribution would be greatly appreciated.

thank you,
kat25

 

[psemianonymous]

The tradeoffs are:

Benefits of monolithic MDB:
1. Ease of design. One MDB. No dealing with linked anything.
2. Ease of portability (i.e. to users' PC if they want a local copy for any reason) - this may not be a benefit


Benefits of split db:
1. When corruption occurs, it will more likely occur on the users' front-end than on the shared back-end. Also corruption (apparently) occurs more often on a shared frontend/backend than on fifty single-user front-ends. This should be a double-item. This in itself is perhaps worth the entire front-end setup.
2. Speed boost (not too significant, but not too small either) moving the front-end to the users' PCs.
3. The ability to use a compiled MDE file - useful sometimes
4. Form size/position is unique for each front-end so you can have one user with everything maximized and one user with tiny forms...hopefully you see what I mean.


Detriment to front-ends:
1. Updating front-ends is a pain. There are utilities and such, but it's still a pain compared to the monolith MDB.
2. Developing is tougher/more of a pain in general with frontend/backend type development. I seem to always forget to re-set my table links when working on my "prototype" and often send junk test data into the production database on accident.


I'm sure I missed a lot of items.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[JimInKS]

I pretty much agree with foolio12.

The one thing I didn't see mentioned is that using a split db makes program updates much much easier, as you can swap in a new FE without affecting the users data.

Corruption is also a biggy if this is a multi-user database. Most of my expierence is with Access 97 and splitting the db seems to help prevent corruption for 97. 2000 and XP are supposed to be better in this area, but I don't have enough experience with them yet to make a determination.

 

[kat25]

Thank you for your information.
I'm still wondering....is it best to have the application database and the tables database both on the server or just the tables database?

thanks.

 

[JimInKS]

kat25,

The only real advantage of having the FE on the server is ease of deployment.

You will normally get the best performance if the FE is installed locally. But in many instances you will not be able to tell the difference. Some depends on the network load and if it is 10M or 100M.

If you are multi-user and users are sharing the FE on the server corruption of the FE becomes an issue.

If the number of simultaneous users and transactions are fairly small, say no more than 5 users and moderate to light use, and your network is fast, I would try the FE on the server.

One thing I have found that really seems to prevent corruption on the server is to disable oppurtunistic locks on the server.
You might look at this MS article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;300216#11

 

[kat25]

Thanks to all for your most helpful
suggestions and information.

 

[Hap007]

kat25,

I think everyone has given you sound advice, but...

You almost never want your Frontend and backend in one database.

What happens if you are in need of doing modifications to the programs. Do you then tell all your users to stop using while you make changes to the programs, I do not think that will fly. So you use a copy, and then when you are done modifying, well now the data is out of sync. Spliting Data and programs is a must in any industrial strength type application.

Second, I beleive that you never want your Front end on the server, unless each user has their own copy on the server or unless only one person at a time will run the program.

Access Frontends are made up of tables, queries, forms, code, shared variables and the like.

Say 2 users run the same report at the same time, so they enter a different date ranges, well only one report will run correctly, the other person might see a report with the wrong dates. Say a developer stores a temporary value in a global field, well if 2 users are using the same form that uses the same global variable, well something bad is going to happen.

The point here is, VB, C++, these languages create executable files that are loaded into memory at runtime. So users can run a common executable from a shared network directory. Access is different, it stores runtime events in internal tables and local values. So if 2 or more users run the same logic at the same time, bad, unpredictable things can happen. Not all the time, just at the inconvient time.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[kat25]

Hi Hap,
Thanks for the information.
I appreciate your timely response.
kat25

 

[sohunter]

HapOO7 wrote "Second, I believe that you never want your Front end on the server, unless each user has their own copy on the server..."

That's exactly what I was thinking of doing, so that I don't have to go to each user's terminal when I've made an update to application. Just make the necessary number of copies (each user would have a short cut that pointed to their copy) whenever I've updated the application design. I suppose I'd have to know if a particular user had their copy open, before I replaced it.

Does anybody else out there do this, and is there a slow down to speed with this approach?

Also, the one advantage to splitting I didn't see above is record locking. I use "edited record locking" generally ... don't know if this is even useful (correct me if I'm wrong) if the database isn't split.

 

[psemianonymous]

Everything depends on where the bottleneck is happening, but yes, putting the front-end on the server will slow your users some. Not as drastic as having the *back-end* on the server--when doing development on my hard drive I get extremely zippy speeds when the front-end/back-end are both local. So I'm saying that you will notice a slowdown, but not a huge one.

2. There's no difference between record locking on an integrated MDB and record locking on a multi-front-end deal. They both use the LDB file to lock the specific page you're editing.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[befine]

I have a similar situation with an addition. There are now some people outside the company who want to view some of the info in the database. The contact info only. They will be accessing the data thru a vpn. I could easily build another Access FE linking to just the contact tables.
However, that creates some more problems, security, updates, speed, etc etc. What I am thinking about doing is converting the data mdb (on the server) to MSDE. Using the Access FE for internal viewing and an ASP.NET page for viewing outside the company.
Does this sound reasonable? Any ideas about how much of the viewing.mdb forms/coding might need to be changed? There are 5 tabs each with 2 subforms and lots of coding

Thanks
Befine

 

[Hap007]

I agree with Foolio12,

When ever you run from the sever, you will see a slowdown.

Consider this, you open a form with code, well although you may have the frontend on the server, executable (Form and code) actually run on your local CPU, so you must read all that over the network. If temporary files are created, then, a read to your local pc CPU happens, data is then processed and then sent back over network to be stored on the server. This is why it is better, perfomance wise, to have your frontend local. (Unless you are using Terminal Server). Under Terminal Server, application runs on server and displays in your windows browser. But, you need Terminal Server on a strong server.

To ease automation of Frontend changes, here is one way to handle it:
1) Always have the Frontend on each user's PC located in the same directory.
2) Refresh all links from the frontend to the live db(backend) then copy that mdb/mde to a common network directory.
3) Copy the revised Frontend from the network common directory to each PC.
Note: Step 3 could be automated by a shortcut to a batch file or to a small copy utility setup on each user's PC.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[Hap007]

befine,

Terminal Server is exactly what you need to solve your problem.

Then, your users, outside of the office, can run the application using their web browser.

and... best of all, they will not even need to have MS Access on their PC's, everything will reside local, in your office on the 'Terminal Server' box. And their performance, over the internet, might be better than your local user's.

Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[fredk]

This is a good thread. I currently have a back end and front end on the server. I have multiple copies (one for each of 9 users)of the front end.

Actually, speed is an issue. I previously had the front ends on the users computer, this was still slow.

I am actually looking into moving the back end to sql server. Will this increase the speed significantly?

Thanks!!!

Fred

 

[Hap007]

Fred,

Yes it will.

You are going from a Volkswagon to a BMW.

SQL Server is a Server APP hosted and run using the Server CPU.

Access is a File Server Solution that uses the Server for file storage, and the local PC for data Query/retrieval. So, in an Access backend version(File Server), no matter where your FrontEnd and Backend is located, your own PC must read and process indexes and data from the backend. All this traffic goes across the network and uses your local PC to process (Unless you are using a Terminal Server environment).

1) In a SQL Server box, you basicly pass commands to SQL and SQL returns only the data that you requested.
2) By having SQL Server use it's PC for the selection, will reduce your CPU requirements and network traffic.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[kat25]

When I started this thread, I received some very good information and the good information just keeps adding to the thread.
One of my original concerns was having the front-end on the server. One of my coworkers really stressed this as the best way versus having the front end on each individual's pc. Now....I'm beginning to wonder if the front-end would not be better on the local pc. Basically, the design is done. If changes were to occur, I could handle the revisions by making site visits.
With all that said, and please feel free to let me know if I'm going down the wrong path with the front-end residing on the local pc,.....can someone help me to understand what steps/process I need to do to have 15 local pc front-end(s) linked to the server back-end? I don't understand how to ensure that my user at each of the 15 different sites can access/link to the tables on the server.
Again, thanks to all for your help.
Kat 25

 

[fredk]

Thanks Hap007 - I have to do something to speed thing up. I have about 5 users in my office and they have no problem, the users in the remote office experience significant delays.

How difficult is it to transition the back end to sql and still use access as a front-end - I understand there are some wizards that will help but how comprehensive are they?

I just received my sql bible today so I am going to start my research but any advise would be greatly appreciated!!!!

Fred

 

[Hap007]

Fred,

To Convert Backend:
Access 97 - Needed to Upload an Access Upsize Wizard
Access 2000 - Had a Access to SQL Upsize Wizard, but it did not work
Access 2002/XP has a good Upsizing Access to SQL Wizard that works.
Open your backend then
Tools > Database Utilities > Upsizing Wizard
Note: You will need SQL installed.


The Frontend only needs to be Modified to handle SQL as opposed to Access. Very similiar, but....
1) Converting the Backend to SQL is only the first step
2) Then you need to refresh all links to the new SQL Backend.
3) Then you need to test code.
4) There are 3 areas to watch for.
a) When you open recordests you will need to add 'dbSeeChanges' to the record set code in VBA
b) In access, autokeys return values as soon as you enter data into any field, but SQL doe not return AutoKey value until the record is saved. This can cause an interesting scenario when coding forms and subforms
c) Features like DLookup do not work on SQL backends.

But, once converted, it is worth the effort.
And once you have mastered the upsizing, you can do it for others. That is how I earn some of my pennies, upsizing Access to SQL.


FYI: To speed up your remote users, you might want to place a terminal server box at you local office, and have them access the program that way. This is an easier/quicker fix. Then you would have more time to convert to SQL.


If you go to my web site's 'links' page, find and follow the Online SQL Training, this will give you some basic SQL Knowledge

http://www.fcs-software.com/fcslinks.html

Enjoy and Good Luck,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[Hap007]

Kat 25

I am confused, are your remote sites on your local network.
If they are on your local network, and can run an Access frontend db on the network, then linking to a Backend would be no problem.

But, if your sites are in a different city or state or country, well that is a completely different issue.

If the sites you speak of are 'Remote', then you also would benefit from terminal server.

Note: I sound like I am promoting/sell Terminal Server, I am not. I just think it is a slick product when used correctly.

Have a Great Day,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[psemianonymous]

The only benefit to having a shared front-end is simplicity for you. By this I mean that you won't have to ensure that 50 front-ends are installed locally; you can simply look at the network directory and say "yes, it's here". Updating involves copying over one file, and you're instantly sure that everyone will be using the new client.


Now. There are processes that you can implement (by yourself) that will update a user's front-end when they open the database. There is an auto-front-end updater, which is written by one of the Access MVP's, that I can't remember for the life of me where it is. But I know it exists.

There are threads on auto-updating the front-end, usually suggested through batch files.


Everything else about shared front-ends is a negative. Speed, corruption issues, "form resize" settings (yes, the one user with a 1600x1200 resolution sets the form to fill up most of their screen, meanwhile the blind old man with 640x480 sees 1/8 of the full form).

It boils down to one huge positive versus a lot of smaller negatives. You balance.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm