After upgrade to dIfferent front-ends noy able to alter clients 1

[johnbrugman]

Current situation: Since i have changed the mrs.mdb with 25 users to aan front-end/back-end application (_be) I have problems with adjusting the client. I made a client for each user. I thought that I could alter a sample front-end easily but my client wil hang after I for example change some code behind the main form. I also devloped the changes in a seperate environment (succeeded) and then try to to export it to the live application. This always leads to a slowing down of the front end of about 3 minutes. The changes I made to the front-end sometimes works 1 time and after closing and trying to open the front-end again leads to the already mentioned hangup. After 3 miutes you can log on but i don't understand the delay. Used OS: NT, W2K, Office2K and OfficeXP

 

[thornmastr]

John,

I’m really not sure what you have done, but let me give you some information which may be helpful to you.

The concept of front end (FE) back end (BE) configuration is to have one database containing only tables. Nothing else. This database becomes your back end and sits on a server. Your forms, queries, modules, and macros are all in the front end. If you need to build temporary work tables they are always built in the front end. The front end never resides on the server. Every user has a copy of the front end installed on their own PC. The FE links to the tables in the BE. That is the basic setup for a networked multiuser Access application.

There are only two real possibilities of backend corruption with this type of configuration. 1) Cheap and broken NICS which die when data is flowing from the BE to the FE and 2) Users who insist on ending their Access session by rebooting the system during an update occurring on the BE.

One of the major benefits of a FE for every user is how easy it is for you, the programmer to design, code, and download changes to forms, code, etc. You can have your users download updated versions of FE’s as needed. As the programmer you should have a designers copy of the FE which can be seen or used by no one but yourself.

If you are experiencing intermittent slowdowns in retrieving data from the BE when your FE first starts create what I call a connect table on the BE which is empty or has one record having only an autonumber. Build a form attached to that table which resides on the FE. In the start up routine of the FE, open that form hidden. This will keep (force) a constant connection between the FE and the BE. When you application closes, you would, of course, close that form.

I hope this addresses at least some of your questions and concerns.

Robert Berman

 

[johnbrugman]

Robert,

I read your reply with much intrest. The explanation of the advantages and working of a FE-BE application is exactly in accordance with my thinking and wher e therefor the reason to choose for splitting up the single mdb file. However I encounter indeed a breakdown when opening the FE which resides on the local PC on any particular time. The last remark about the connect table may be of any help. Can you explain me more about that?

Ik already have in my Start Up e from opened and on that form I have a On Timer event running.

The strange thing is that I can adjust several things in my FE via diffenrt ways (create in local databes and export, import from the local-FE to the local databes, Ctrl-C, Ctrl-V an object) but sometimes after a backup and restore the main form isn't opened within a reasonable time anymore..

I hope you van help..

 

[thornmastr]

John,

I will be more than happy to give you as much information about “constant” connection as you would like, but first a few comments about Access 2000 and your configuration. These may eliminate your problem. I’m not sure if you are running Access 2000, or Access 2002, but it sounds like you are using Access 2000 since a client of mine had a problem very much like yours and this information may well solve your problem as it did his.

Make sure you have downloaded Microsoft Office 2000 SR-1/SR-1a update and that it is installed on every client machine running your application. Please note this is not Sr-1, but is in addition to SR-1 and fixes a number of quirky behaviors in Access 2000.

Make sure your client PC’s are running Jet 4.0 SP6. If not, download it from the MS page. The following dates and sizes refer specifically to Jet4.0 SP6.

File Name Version Size
-----------------------------------------
dao360.dll 3.60.3714.5 557,328
expsrv.dll 6.0.72.9414 397,072
msexch40.dll 4.0.5325.0 512,272
msexcl40.dll 4.0.5919.0 319,760
msjet40.dll 4.0.6218.0 1,503,504
msjetoledb40.dll 4.0.5919.0 348,432
msjtes40.dll 4.0.5914.0 241,936
msltus40.dll 4.0.5325.0 213,264
mspbde40.dll 4.0.5325.0 348,432
msrd2x40.dll 4.0.4910.0 422,160
msrd3x40.dll 4.0.4325.0 315,664
msrepl40.dll 4.0.6226.0 553,232
mstext40.dll 4.0.5325.0 254,224
msxbde40.dll 4.0.5427.0 344,336

And last but not at all least, make sure your network administrator places the backend database as close as possible to the shared root directory. I refuse to install a BE deeper than three folders. If you think about it, it makes a lot of sense. When your first form opens, Access walks the directory tree to link to the BE. If you are buried in the bowels of the tree and you have a burst of network activity, the time it takes to open the form will increase and then decrease until it looses the connection and attempts to reestablish it again when it starts its walk down the tree again.

Let me know if this information proves to be helpful.

Robert Berman

 

[votegop]

Robert,

Your info. to John is most helpful. Please explain one point for me...what is the big disadvantage to having the front-end on the server with the back-end? Is the size of the database the determining factor in whether or not you can access the FE on the server with reasonable speed? If its a small database, does it matter?

Thanks.

Jay Evans

 

[thornmastr]

Jay,

Let’s discuss this from the front end point of view.

First, network considerations. Think of the time it is going to take to open the form from the networked FE and then send that form to a client PC, even with minimum network activity. Now, consider only three users doing the same thing at close to the same time. You will probably have users screaming about how long it takes to open a form. Consider a user who decides Access has locked up because his response time is slow and he does a hard reboot which, in all probability, will corrupt your shared front end. Remember that Access corruption across a network is almost always caused by (1) a faulty NIC, (2) A user doing a hard reboot during a network transfer. (3) a developer modifying a form that is in use by another user or modifier.

A quick summary of advantages to splitting your database as written by Litwin/Getz, and this quote is from them, not me.

Better performance (especially user interface performance).

You can create temporary tables within the application database stored on each workstation and not worry about naming and locking conflicts for temporary objects.

Splitting the database makes updating applications easier because the data and application are kept separate. Changes to the application can be made off site and merged back into the application database without disturbing the data.

The major disadvantage of this approach is that Access hard-codes the path to linked tables. This means that if you move the data database, you have to fix the table links.

I hope this answers your question.

Robert Berman

 

[votegop]

Robert:

That does answer my question. Thank you. It also brings up another novice question, if you don't mind...

If I have a split database to distribute to a client via mailing them a CD (CD has an install utility to put the FE on the workstation and the BE on the server)...someone still has to manually use the Linked Table Manager on every workstation to establish the link between FE and BE, correct? Is there a good way, even with a 3rd party application, that you know of to automate this process?

Thanks a million.
Jay Evans

 

[thornmastr]

This was, for some time, a problem, especially in the early days of Access 2.0, but the more information MS began to release as to just how powerful VBA was going to become, it became quickly obvious that it is possible to establish functions that would handle the linking. If I remember correctly there are some very good techniques to do this on the MSKB under Access. If you need to know how to do it, I can send you one of the functions I use. Let me know.

Robert Berman

 

[votegop]

I'm perusing the KB article, and have downloaded the Solutions.mdb sample database, which has a "link tables" module. It's a module with alot of code, but I may be able to make it work. If you have something that is easy to send, I'd appreciate it. It helps me to have multiple examples, since I can only muddle my way through other people's VBA...finding and replacing the appropriate references.

Jay Evans
votegop@hotmail.com