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

 

[fredk]

Thanks much for the information - I have several databases and I am going to convert the simplest first - I have one database that has a lot of code - I will save that for last!!!!


I appreciate all of the help!!!

Fred

 

[fredk]

I have one other question about converting to sql - In theory, after the back end is set up on sql, is the front end basically "linked" to the sql server? Is it an odbc connection?
Thanks!!!!

Fred

 

[votegop]

Hap:
Your advice is outstanding! Thanks for contributing so much to the forum.

One question about your post:

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.

I do something similar, but maybe inferior...I don't refresh links until the user opens the .mdb. This seems to cause severe slow-down when more than one user is logged on.
If I do as you describe, and refresh links prior to copying it to each user's PC, will the refreshed links still be good once copied to the user's PC? I guess I'm asking if a linked FE cares where it is, as long as it knows where the BE is on the server?

Jay

 

[Hap007]

Jay,

You are correct.

If you have several users linked to the same backend, then as a user refreshes the links, the 'link process seems to take longer('severe slow-down' describes it pretty well).

What I did was make sure that the backend was mapped exactly the same for each user. I then placed the frontend in a directory (Directory was called the same on each user's PC. But before I did the copy of the frontend to the user's PC, I refreshed all the links from my test backend to the live backend.

What this achieved was each User did not need to refresh the links, only I, the developer, refreshed once to the live database backend and then copied the frontend to each user's PC.

Now, The frontend has code to check and see if the links need to be refreshed. The links were refreshed only when needed. This was done by the program iteself. I have an example of how I test to see if this is needed in my Switchboard example.

What I did was define a table to hold records that named a backend and provides file names, directories and drives. Then the frontend user can pick which backend to link too. The links are only refreshed if the backend selected is different from the previous selection or if the file location changes. Works like a charm. Infact, my example allows the user to switch from an Access backend to a SQL backend and back.

Hope This Helps,
Hap


Access Developer

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

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

 

[Hap007]

Dear Fred,

Regarding your question:
"In theory, after the back end is set up on sql, is the front end basically "linked" to the sql server? Is it an odbc connection?"

The answer is no. After you upsize the backend, then you must link the frontend to the new SQL Backend using ODBC source instead of Access.

Here is the trick to watch for:
Say you have a table called 'tblCustomers' in your Access Backend.
You link to the backend and the table shows up as 'tblCustomers' in the Frontend.
When you designed your forms and queries, you selected 'tblCustomers'
So, now all your work is done.

Now you upsize to a SQL backend,

You must then delete all the Access linked tables and relink to the SQL Server Backend using ODBC.

...but, when you select 'tblCustomers', the new link will show up as 'dbo.tblCustomers'. Well your Forms and queries will not find this source, so after you do the 'link', then rename 'dbo.tblCustomers' to 'tblCustomers', and all your code will be happy.

...or, you could write code to relink from and Access backend to a SQL backend in VBA, but the Access 'Link Table Manager' will not refresh from Access to SQL. I have an example of how to do this is my Switchboard Menu program. Someday soon, I will try to write a FAQS on this topic.


I Hope This Helps,
Hap



Access Developer

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

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

 

[Kanga35]

Quite interesting Hap as my main DB has table now with over 80 000 records and it's going to be time to go SQL. Thank you for your lights...
My DB has everybody downloading a front end on their own PC through the microsoft setup option. My table are on the server and I have added one special table witch I call &quot;WhatsNew&quot; it's displayed through a combo box on the main form which open at start up (if not then I do not give any right to the user). As an add on the last field of that table contain a Revision number. In code of that main form I hard code a revision into a variable. Then I compare the data rev and the form rev and if (form rev)<(data rev) I display a message requesting the running of the setup to refresh the engine and when acknowledge I exit the DB. Depending on the rev in the data I can force or not an update of the front end.

 

[smitan]

In the case you are interested in a little program that copies a new version to a local PC / homedirectory you might send me a mail: hvd@activesearch.dk
The program I have uses a ini-file that contains some settings about where the new version is stored and where the FE has to be copied to and then started.
If the new-version folder doesn't contain a newer version then the one in the local folder is started.

About moving from mdb to sql-server included for me also moving from FE.mdb to FE.adp
Most of the work was rewriting evrything from DAO to ADO and taking the functions out of my queries.
The whole project was done in 2 month with a mdb having about 150 queries, 125 tables and 175 forms plus one weekend for the dataconversion, which I did with a sql-server linked to the original data.mdb

All the best,
Hans

 

[fredk]

Thanks Hap - I appreciate the insight. Just a few more questions - You said that by re-naming my tables the connection to queries and forms would be ok - Am I just renaming them on the access front end? I am guessing they would still be the same in sql, just my front end would have a different name, is that correct?

The problem I have is that the sql server is &quot;controlled&quot; by another area and I would have to work through them to get this donw. This brings up two concerns for me. Once the back end is transferred to sql server and I update the front end via ODBC connection... Can I continue to add/change my queries on the front end without doing anything on the back end (just as I do now) - I am concerned that each time I want to create a new report I will have to reach out to the area with control of the sql server? Obviously, if there was a table or field change I would need to.

Also, in our meeting I provided a samle database for them to review. One of the people in that area questioned why my forms (data entry form) was bound directly to the table. She made a very big deal about it and said that it really should not be that way.

I have been working with Access for a little while now and I continue to learn through this site and various books I have. I never really heard this before.... What are your thoughts on that?

I appreciate all of your help!!!!

Fred

 

[JeremyNYC]

Wow. I couldn't bear to read all of the stuff here because I think some of the advice is really way off the mark. Sorry. I'm sure people will be angry at me for my response, but I find it hard to believe that any professional access developer would recommend having the FE and BE combined or having just one FE on the server. These are both _really_ bad ideas.

It's not so hard to write code that checks which directory you're in and reconnects to the appropriate back end based on that.

It's not so hard to write a batch file that does all of the updating of the front end so that you never have to visit a user's PC, and it only does this if the user doesn't have the latest version.

Unfortunately I just got buzzed for a meeting, but if anyone's interested I can certainly share the code to do these things.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[kat25]

&quot;,.....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.&quot;
*************************************************
Jeremy,
I would appreciate it if you would share your code to do
these things.

Do you need my email address or is the code shown on the
ABCDataWorks site?

Thank you,
kat25

 

[fredk]

Jeremy, I would definately be interested in that code.

Thanks

Fred

 

[JeremyNYC]

Well, there's some code on my website, in the deploying databases pages in the Developers' section.

Here's a clip from a post I put up a few days ago about using batch files to do the deployment. I'll dig around a little and get the linking code I use a bit later.
--------

I use batch files to do all this, so that if there's a new version to be rolled out, the user doesn't have to sit through the opening of the database, get the new version, and then sit through the opening of the database again.

Here's a batch file I use for one of my clients. It relies on having a version file that tells me what version is being used. All I have to update when there is a new front end is the name of the version file and the two references to it in this batch file (in one place--the server directory that holds these files).

There are a couple of pages on my website that detail this process, with a slightly more complex batch file.

Jeremy

rem make the directories (no harm if they're already there)
c:
cdcd &quot;Program Files&quot;
mkdir &quot;NRP Database&quot;
cd &quot;NRP Database&quot;

rem If the latest version file is on the PC, just start the database
if exist &quot;NRP_FEVersion.1.83.txt&quot; goto Startup

rem copy the images and shortcuts
copy &quot;\\Nrp\C\NRP System\NRP_FE.bmp&quot;
copy &quot;\\Nrp\C\NRP System\ReportFooter.bmp&quot;
copy &quot;\\Nrp\C\NRP System\ReportHeader.bmp&quot;
copy &quot;\\Nrp\C\NRP System\NRP Database.lnk&quot;
copy &quot;NRP Database.lnk&quot; &quot;C:\Documents and Settings\All Users\Start Menu\NRP Database.lnk&quot;
copy &quot;NRP Database.lnk&quot; &quot;C:\Documents and Settings\All Users\Desktop\NRP Database.lnk&quot;

rem copy ABCD Library
copy &quot;\\Nrp\C\NRP System\ABCDLibraryXP.mde&quot;

rem Copy a new front end
copy &quot;\\Nrp\C\NRP System\NRP_FE.mdb&quot;
copy &quot;\\Nrp\C\NRP System\NRP_FEVersion.1.83.txt&quot;

:Startup
rem fire it up
&quot;C:\Program Files\Microsoft Office\Office10\msaccess.exe&quot; &quot;c:\Program Files\NRP Database\NRP_FE.mdb&quot; /wrkgrp &quot;\\Nrp\C\NRP System\NRP.mdw&quot;

This batch file does a lot more than some people will need, but it shows a bit of what can be done. Most of it is pretty easy to understand. If not, check out those web pages I refered to above. If that doesn't help, feel free to put more questions here.

As I said, I'll get to the linking code in a bit.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[fredk]

Thank you very much Jeremy, I appreciate it - Can you provide me with your thoughts on my posted question about forms being bound directly to tables..... ?

thanks much!!!!

 

[kat25]

Jeremy,
Thanks for the code and all the helpful information.
Great info. on your website.

kat25

 

[JeremyNYC]

Fred,

People who work with databases other than Access generally scoff at the idea of bound tables, as you've just found. I learned Access from a VB guy, so for the first few years I used only unbound forms. I've since switched to using mostly bound forms. The reason these people don't like bound forms is because they don't know them. There is a big adjustment to be made moving in either direction, though I would argue that there's more to learn about bound forms than unbound forms.

In any case, bound forms are plenty safe, though not suited to _every_ use. If you were running a mission critical app with bound forms you might have some concerns. But then, if you were running a mission critical app with Access you would have lots of concerns anyway.

Bound forms are fine.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[fredk]

As always, thank you very much for your help. I appreciate your assistance!!!!

Fred