Configuring a client/server db

R

Rob.Mills

Registered User.
Local time
Yesterday, 23:03
Joined
Aug 29, 2002
Messages
871
Hey all,

I am getting ready to setup a program for approximately 12 users. I know that's pushing the limit for Access but the amount of data will probably not come close to the size limits.

My question is what is the best way to configure things? Is it better to have the backen, frontend, and workgroup file on the server and setup shortcuts on everyone's desktop? Or should you only place the backend on the server and the frontend and workgroup go on everyone's computer? And if I use the latter and want to either change something in the frontend or add a user to a group do I need to redistribute everything?
 
The preferred way is to leave your tables in the back-end and everything else in the front-end. The reason being, Access is a "not" a "client-server" program. If you leave everything in the back-end, you'll find that your speed will suffer because all objects called by the form you are running (ie. forms, tables, queries, macros, etc.) must be transmitted from the back-end to the users' PC's creating a large amount of network traffic.

You can leave everything in the file server and eliminate the traffic problem by installing a "thin client" software like Citrix.
This will allow your users to run the application in the server and transmit only data and screen updates via the network, thus eliminating the transmission of all database objects via your network.

Citrix could be an expensive proposition though, but will simplify program updates because you only have to do it in one place --- your server.


edtab
 
From one of my previous posts. Hope this helps. I know that some are real favorable to setting the db only to copy over when the version has changed but it seems like a lot of work just to save a couple of seconds for the download.

Your shortcut will need to specify the security file to use. I would put it on the server for access for all. Because you are using dial up access as well as traditional you need to split your database. It is very easy to do by using the wizard. Create a batch file to download the frontend from the server every time someone starts the database and it will be much easier to provide updates. Load the security file on the server in the same directory. (I copied the following from a previous post of mine that details how to set up a server based database).

Link front end to the backend (this is where you will have some work to do with you dial up machines. Do a search for auto linking of tables and build that code into your database. With LAN based machines if you set yours up the correct way you won't have to worry about it as all those machines will be the same. There is a lot of posts concerning setting up security, I urge you to read them. Also, check out Microsoft: Other Topics Faqs on www.Tek-Tips.com for more on the same subject.

Create a batch file to copy the front end from your server to each users machine. Then enter the command to start the database. Distribute the batch file. This way when someone executes the batch file they always get the appropriate front end in the latest revision level. Below is a sample of my batch file.

md "c:\my documents" (I do this because some machines are Win NT and some are Win 2000, if c:\my documents exists it does nothing)
copy s:\everyone\ecndatabase\BereaECN.mdb "c:\my documents\*.*" (this copies my database front end from the server to the users my documents folder)
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "c:\my documents\BereaECN.mdb" /WRKGRP "s:\everyone\ecndatabase\Secured.mdw" (this starts access, loads the database with the correct security workgroup)

Autoeng
 
Rob:

Some things that you will want to consider adding to your db to make it better in a distributed setting are:

Auto form resize for users that use a different screen resolution than one it was developed in. See www.developershandbook.com

Automatically log your users off for updating of the backend. See www.rogersaccesslibrary.com

No menu bars so that users are forced to use the db as intended. You can search this website for that.

Autoeng
 
I'm doing the same as Autoeng except that the shortcuts on the users desktop access the frontend thru a security file that is on the server. The front end file is also on the server. The reason is that in this arrangement everyone absolutely uses the same front end.

The second is that, because the server is merely acting as a file server anyway, the performance is pretty much the same. The recordsets still have to cross the network in either case. Granted the new forms will have to cross the network in my setup as the users switch forms but this has only a minor effect on overall performance. This is atleast my perception.

Personlly, I think that ultimately terminal services will provide the best result when you consider cost.
 
Thank you for all the great information!! I like the idea of putting the frontend on everyone's desktop and then setting the shortcut to copy the frontend from the server. Didn't know you could set it to do that. Man, I've learned so much from this site.
 
AutoEng,

I'm new to creating batch files and I tried what you suggested using my pathnames but I wanted to change the location of the file on everyone's computer because the 'My Documents' folder is under each user's name. I tried using the 'Program Files' folder. Here is what I wrote out in the batch file.

md "C:\Program Files" copy J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb "C:\Program Files\*.*" "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Program Files\PymtCalc.mdb" /wrkgrp "J:\Tax\Outsourcing\Payment Calculator\CONSECOWRKGRP.MDW"

For testing I have the batch file located on my desktop. When I run it no db opens. All I get is two new folders on the desktop.

:confused: :confused:
 
I'm not seeing anything incorrect with your batch file but what folders are being placed on you desktop? Are you sure the workgroup is in "J:\Tax\Outsourcing\Payment Calculator" and not in "J:\Tax\Outsourcing\Payment Calculator\Calculator" like the database is?

Also, if every machine has a "c:\Program Files" directory (and probably does) you can remove the "md c:\Program Files" line from the batch file.

Autoeng
 
This is really weird. I verified the pathname of the workgroup which is correct. I eliminated 'md C:\Program Files' like you said.

Now the only thing that happens is it quickly opens an MS-DOS window and then shuts it again. That's it.

Just to be sure, to make a batch file you just type it out as a text file and then save it with a .bat extension, correct?
 
You are correct on how to create a batch file but you can't use MS Word as it has formatting no matter how you save it. Use Notepad instead and see if that helps.

Autoeng
 
Well damn Rob, I'm stumped. Try changing
"J:\Tax\Outsourcing\Payment Calculator\CONSECOWRKGRP.MDW" to
"J:\Tax\Outsourcing\Payment Calculator\CONSEC~1.MDW" in case ms dos is not recognizing the beyond eight file name.

Autoeng
 
I'm stumped too. I tried your last suggestion and that didn't change anything else either. Oh, well. What's a developer to do?
 
What's a developer to do? Start breaking it down and see what the problem is. Make a batch file just to copy the db to your directory. Make another to start the database with the correct workgroup file. Did either work? You are entering the text into the batch file as one long sentence, correct?

Good luck,
Autoeng
 
Ok. I broke it down as u said.

First I tried using a batch file to just open the db:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" /wrkgrp "J:\Tax\Outsourcing\Payment Calculator\CONSECOWRKGRP.MDW"

Worked good.

So I tried just copying the db:

copy "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" "C:\Documents and Settings\All Users\Documents\*.*"

Worked good as well.

Then I tried combining commands:

copy "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" "C:\Documents and Settings\All Users\Documents\*.*" "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Documents and Settings\All Users\Documents\PymtCalc.mdb" /wrkgrp "J:\Tax\Outsourcing\Payment Calculator\CONSECOWRKGRP.MDW"

Nothing... How could this be? Is it possible that it's trying to open the db before it has time to copy?

Thanks for your help.
 
I don't think so. Just for giggles in the copy batch add a call for your start batch and see if that works.

Example:

copy.bat = copy "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" "C:\Documents and Settings\All Users\Documents\*.*" start.bat

You might have to enclose the start.bat in quotations but not sure.

Autoeng
 
Seems like everything you give should work. I tried add the start batch at the end, then with quotes, then with 'call' in front. Nothing. I know you told me to break things down and figure it out but I really don't know where to go from here.
 
Me neither. I'm stumped. Surely someone else is reading this and going "What a bunch of idiots, they can't even write a ms dos batch file".

Send me a private message with your email address and I'll send you a zipped batch file that I create. Who knows, it just might work!

Autoeng
 
Hi Guys,

Just been reading through your thread, and the only thing I spotted was AutoEng stated "you are entering this as one long sentence". I may be wrong, but I thought that each new command had to be on a new line, so you need a CR after the copy command. Hope this helps.

Pete
 
Do you mean like this?

copy "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" "C:\Documents and Settings\All Users\Documents\*.*" CR "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "J:\Tax\Outsourcing\Payment Calculator\Calculator\PymtCalc.mdb" /wrkgrp "J:\Tax\Outsourcing\Payment Calculator\CONSECOWRKGRP.MDW"
 
You must log in or register to reply here.

Similar threads

raziel3
Where does the Frontend store the Backend's address?
Replies
10
Views
770
isladogs
isladogs
J
How to see the UN and PC Name of locked Records
Replies
14
Views
374
jmark@stayintouch.us
J
G
Upsized Back End to SQL, does Caching need to be removed?
Replies
13
Views
490
Pat Hartman
P
S
Solved Managing SQL Server Logins in MS Access Frontend
Replies
19
Views
1,135
AHeyne
A
D
Darshan Hiranandani : How to Delete Access Lock Files and Prevent Future Issues?
Replies
2
Views
221
DHookom
DHookom

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom