Forms Incrediblly Slow to Load 2

[TimTDP]

I have an Access 2000 database that I want to use in a multi user environment. I have split the database into a front end (forms, modules etc) and a back end (tables only). The back end resides on a windows XP PC. On the user PC's I have mapped a drive to the PC holding the back end.

When I run the front end on the PC holding the back end is it very fast. However when running the front end from a remote PC the forms take an incredibly long time to load. Even if only 2 user's are using the database.

There are currently only about 500 records in the database! The size of the back end is only 1.2 Meg. The front end is 5 Meg's but this is caused because of a graphic file in one of the reports. Without the graphic it is 2.3 Meg in size.

How do I determine what is causing the database to run so slowly?

The network is running at 100 Mbps

 

[genomon]

I would start looking for bottlenecks in your network.

The early bird may get the worm, but the second mouse gets the cheese in the trap.

 

[TimTDP]

Thanks
How do I look for bottlenecks?
Do you know of software that I can use?

 

[genomon]

I am just a lowly programmer. When my apps or DBs suffer network traffic and latency issues, I go and cry to our support guys (that's what they get paid for).
Sorry I cannot be of more help!!

The early bird may get the worm, but the second mouse gets the cheese in the trap.

 

[MajP]

Tim,
With A2K there are three widely known bugs that will grind your application to a halt in a FE/BE setup. Read this post and tackle the top three first. The others are nice to take care of, but my money is on one or more of the top three.

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

 

[MajP]

This is also very good:

http://www.fmsinc.com/tpapers/faster/index.html

 

[ZOR]

It might be relevant, but when I first made a front/backend db, I could not believe how slow it was until I found this, and hope it helps:

http://support.microsoft.com/?kbid=200600

 

[TimTDP]

Thanks Chaps

MajP, I followed the links and tackled the first three. It helped.

I also use the following code, which ran whenever the database opened, to ensure that the table links were correct:

Dim db As DAO.Database
Dim Tdf As DAO.TableDef
Dim Tdfs As DAO.TableDefs
Dim rst As DAO.Recordset
Dim DataBaseFolder As String

Set db = CurrentDb()
Set Tdfs = db.TableDefs

'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE= T:\Database\CompanyData.mdb" 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table

When the above code ran, the forms were still sluggish to open. If I remove the code the forms are super fast. Why would this code make the forms sluggish to load?

 

[genomon]

Try the code on a machine that has the backend installed & linked locally (C:\....) & see what happens.

The early bird may get the worm, but the second mouse gets the cheese in the trap.

 

[TimTDP]

Hi genomon

On a local machine the forms are fast

 

[JoeAtWork]

In what event did you put the code that relinks the tables?

Personally, I don't think it's a good idea to have such code using a hard-coded path.

 

[genomon]

We've had similar problems. Also one of our mdb's is so large it won't compact on the network, but compacts fine when copied locally. It was explained to me by our support guys & DBA that it has to do with linked tables accessed over the network. I realize that this is not the source of all your woes, just letting you know you're not alone. If you ever find a way around this, please post it back here!
Thanks!

The early bird may get the worm, but the second mouse gets the cheese in the trap.

 

[GingerR]

If I were you, I'd link the tables to the full path of the drive, not the "T" drive.

1) it's sort of cheesy to have your users need to map a share to a certain drive. I like my users to not have to do anything except hit a button. It's also cheesy for you to go around to various machines and map the drive.

2) you won't have to manage this in case a user's machine unmaps the drive from "T".

3) you can get rid of that code forever.

So do this: In your front-end dev copy, go to TOOLS+DATABASE UTILITIES + LINKED TABLE MANAGER. hit SELECT ALL button, and check the "Always prompt for new location" box. When the directory browse window opens up, in the FILE NAME text box, type in the server name/path of your db, i.e.

"\\servername\sharename\"

then browse from there. So instead of the "T" drive being a necessity, the full share name goes in there. This also makes it so the users don't know where the db is (well, they do if they look of course but most don't care or have the time) but at least they don't have it mapped on their machines, another place for them to poke around in that they wouldn't be able to if it weren't mapped.

Anyhow, then make a shortcut, pointing to the db either on that share (again, using the full path vs. the drive letter) or on the C:\ drive, whereever it is you put it.

Good luck.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TimTDP]

Hi JoeAtWork

I have a macro called Autoexec. The macro calls the code from a module.


I have removed the code calling for the re-linking of the tables. It would have been nice to have. I have resorted to educating the end-users to shout for help if they get a error on start. not the ideal solution but better than a slow F/E.

Thanks for all the help and assistance

 

[djayam]

Thie single best solution to this for me was to define all control sources in the OnLoad event of the form. Couldn't believe how much this sped everything up!!

 

[GingerR]

TimTDP--why "educate the users if something fails" - why not just link to the full path as both JoeAtWork and I suggested? Then you never have to mention it or think about it again.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[JoeAtWork]

I have resorted to educating the end-users to shout for help if they get a error on start

99% of time the problem would be with network connectivity, in which case the extra code you have for relinking would fail in any case.

If you really want the program to "self-heal" if the backend gets moved, there are threads in Tak-Tips (and elsewhere on the web) showing functions that can check if tables are no longer linked and attempts to relink them. That's the key - don't go through the long process of relinking unless it's actually necessary.