Forms Hang on Load if no other form with subform is open.

[dgunner71]

This is a really strange issue - when I open my database the only open form is the main switchboard. When I open a search form (which has a subform) it takes about 30 seconds to open. Non-search forms (which also have subforms) take about 5-6 seconds to open. This delay only occurs on the deployment server - there is no delay at all when I open on my laptop.

Now the strange part - if I just keep another form open (on the deployment server) which has a subform on it, the delay goes away. So, to illustrate - if I open frmCustomerSearch with only my switchboard open, it takes 30 seconds to open. If I have any other form open, the delay disappears.

The database is MS 2010 and is split into FE and BE files, both of which are located on the same computer.

As always, any help is greatly appreciated.

Kind Regards,

Gunner

[zmbd]

As you have found, there is sometimes a downside with using the split database over a lan in that the connection can be slowed. This has to deal with your IT hardware.
One solution, as you discovered, is to open a link to the database and keep it open... that however makes the sharing very difficult.
Some ways around this.

Latebind the form to the recordset... that is to say, in the onload/open event of the form, set the recordsource for the form and remove it from the saved properties. I do this quite often... develop with everything bound and then go in and a late bind. This is really helpful if the number of records is large.

Keep a pointer open to the backend...
Seeing as you have a main switchboard.
In the general section
under declarations

Static zPointerToDB as DAO.Database

then in the onload of the form you can

set zPointerToDB = OpenDatabase(zstrName)
 

zstrName is the full path to the backend. You can pull that from the linked tables.

(btw: Once set... use this as your reference in all your RS code! So you wouldn't set a currentdb pointer, you'd just reference this pointer (formname.zpointertodb)
(I'd use something that checked for the object...

(code ommited)
DIM zLocalDBObj as DAO.Database
(...)
if formname.zpointertodb is nothing then
   set zLocalDBObj = currentdb
else
   set zLocalDBObj = formname.zpointertodb
end if
(...)
if not zLocalDBObj is nothing then set zLocalDBObj = nothing
(...)

)

In your onclose/unload even of the switchboard, you should set the zPointerToDB to nothing to release the link.

Get your IT departement to do an infrastructure upgrade. Since my Dept did that and moved everything to Win7 from WinXP I haven't had any such lags - gota love those 10+Gbit routers :)

[zmbd]

oh... and one more thing...
evey user needs a their own copy of the front end.
NEVER have multiple people opening the same front end.
IN FACT, it is preferable to have each user have a local copy of the front end on their PC... there are a lot of ways to automate the relinking of tables (google or seach here on bytes it's been covered many times and there's a ton of code) and there are various methods of deployment.
IN my case, I have a simple series of code that checks which drive the front end is being opened from... if it's anything except a C-Drive path then it asks the user to please close the DB and copy the front end to their local pc desktop.
I've since found some code that write a batch file which I'm trying to get IT to allow to run that will close the db, copy, and the re-open the local copy.

[dgunner71]

Thanks, zmbd - what you have said makes perfect sense. I put the noted code in the main switchboard and the delay is indeed gone now. THANK YOU!

One side note, however - both the front end and back end are on the same computer (not shared across the LAN). Might this delay, however, be a function of an encrypted back end?

In any event, BYTES comes through once again! Thank you!

Gunner

[dgunner71]

zmbd -

Yes - each user will have their own front end - thanks again.

I am deploying this on a server where all users will log in over Remote desktop. Each log in will have a specific front end file associated with a shortcut on their desktop. The reason I do this is first to eliminate the delay and second it makes troubleshooting and updating the front end much easier.

I would appreciate your insight on this approach.

Thanks again,

Gunner

[zmbd]

I really wouldn't load the front end file from the network each time like that unless you have a very up-to-date infrastructure.

Why?

You have to wait for the front end file to load to the local Access engine
Then you have to wait again as the local, sends to networkFE that then sends to backend, that sends to networkFe, that sends to local...
OK, what actually happens:
Local ACC opens the files on the network.
Local ACC opens the links
User opens form
Local ACC opens form from network... wait for load:
Local ACC sees if there is a record set
Local ACC then calls the linked table information from the network frontend
Local ACC calls the linked table information
Local ACC calls for control information (cbo lists etc)
and so forth...
ALOT OF NETWORK TRAFFIC!

The major advantage of having the local PC have a copy of the frontend file is that NONE of the forms and local tables have to be called over the network.

HOWEVER:
When I go to final alpha, I will do just this...
I split
I load the FE from my network drive
and I push the (colourfull word here) to the limit.
I do this because it is the worst case usage of the infrastructure.... before the 10+Gb routers were installed, there would be horrible time delays and form crashes... now with the faster network... I've been tempted to simply start development with a split database over network.

SO, if you have a good, reliable, highspeed, intranet... go for it... if not... IMHO... not the best way to go.

I use versioning numbers.
In the backend, I have a table that has version number
if the front end opens and the versions for front end and backend do not match (or if the the fe<be v# then new fe req, else id fe>=be# then good to open), then there's a message sent to the user to update and the frontend closed.

There are also various methods to deploy front ends... a search will turn up a few examples. I have a good group of people that all I have to do is tell them in an email and make sure the versions are set and they take care of all this themselves (although I've been asked to automate it :) )

[dgunner71]

zmbd -

I don't think I'm explaining this correctly - the front end and back ends are stored on the server. When a user is going to access the db, they will login to the server over RDP so that they are actually working on the server. When the open the FE, they are opening it and using it on the server. Ultimately, there is very little network traffic (just the display).

The network infrastructure is not great to deploy with the front ends on each user's computer. Also, it makes updating problematic for me.

Back to the original issue - your suggestion has worked well but this only works after the first delay. (The first time I open a form, there is a delay - after this, the delay goes away).

Also - I am not releasing the zPointerToDB (setting to nothing) each time I close the switchboard because I am using it to keep the link open (correct?).

Gunner

[dgunner71]

looks like I spoke too soon - my delay is inexplicably back.
sigh....

Also, having trouble editing forms now because it says that the database is not exclusively open. Back to the drawing board.

Gunner

[zmbd]

zpointertodb should stay open until you close the application:

1) If the switchboard remains open then the code works
1a) if the switchboard closes at any point pointer closes and you will lose the connection.
-) so, open a second form, hidden, that has the static varible pointed to the backend... you might try this in a standard module too... I usually use the form out of habit.
-) have in the exit application cmdbutton code to close the hidden form, have the hidden form's onclose release the pointer.
\\
Delay... primary suspect is the infrastructure or method of acccess... VPN and RDP are not your friends.

You MUST NOT allow multiple users to open the same front-end which is what it sounds like... otherwise you WILL have conflicts and data corruption.

The network infrastructure is not great to deploy with the front ends on each user's computer. Also, it makes updating problematic for me.

This can be automated... just search... that what I did... the script version looks really promising and I've been working on a multiple access application method.


OK, concerts to attend.

[dgunner71]

zmbd -

Thanks for all your help today - I removed the encryption from my back end. After several hours of testing, I'm relieved to say this seems to have completed eliminated the hang/lag time issue.

Very best.

Gunner

[zmbd]

yes, general encryption can slow things
You might should take a look at the insight articles covering SHA, MD5, RC4, and AES to at least cover the sensitive data at the individual record level: http://bytes.com/sitemap/f-273.html

[dgunner71]

Will do, zmbd.

Thanks again.

Gunner