bound form tied to linked table slow

[jeffmoore]

I have a form bound to an empty table. Here is the onopen code:
Dim strsql As String
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strsql2 As String
strsql2 = "DELETE * FROM TestRequest;"
db.Execute strsql2
DoCmd.GoToRecord , , acNewRec
strsql = "SELECT LabNum FROM LabNumber;"
Set rs = CurrentDb.OpenRecordset(strsql)
With rs
.MoveLast
Me![LabNum] = rs![LabNum] + 1
.Edit
!LabNum = Me![LabNum]
.Update
Refresh
End With
rs.Close
Set rs = Nothing

table 'testrequest' has at most 4 records
table 'labnumber' has 1 record
all tables are linked to a backend.

The first time this form is open it can take close to 60 seconds for the form to display. the subsequent times the form opens within 2 seconds. Is there any way to get access to speed this up??

TIA
JEff

 

[SteveR77]

Jeff,

One option would be to open the form with the database and have it hidden so when an option to open the form is selected you merely have to unhide the form.

HTH,

Steve

 

[jeffmoore]

Yea I thought of that ... but opening the form also grabs the next lab number. If they then shut down the db that lab number would go unused. That still happens if they decide to close the form in question with out entering data. I just cant figure out why the form should take so long to open. It doesnt happen if every thing is local. which makes sense. The only thing I can think of is that access has a really piss poor data managment system when it comes to linked tables. I wonder if opening the tables in a hidden datasheet view would help???
Jeff
Does access have the same response times if the database where upsized to sql server??

 

[Plumes]

Jeff,

Try unchecking the options in tools> options> general> Name Auto Correct I found this had a significant effect. I leave them checked while making any changes to the db but uncheck them when the db is in use on the network.

Matt

 

[RoyVidar]

The auto correct, and other tips are also available in this access performance faq

Roy-Vidar

 

[scriverb]

I have experienced a similar problem when distributing a DB front-end that had to be relinked through code to each offices back-end local server. Even though the database and tables were exactly the same as those where the testing and development was completed, ACCESS recognized the change in the linked table connection and initiated a re-optimization of the queries that were used to open the form. This only happened the first time the form was opened after database distribution. But, the time delay was signficatant.

If your RecordSource for the form is a query and you are in anyway change the linkage connection id then this may be your problem. My database would be slow the first time and then after that be fast as expected. The query optimization process(Rushmore) is quite extensive as it takes into affect number of records, indexes, sorting, etc and configures the query to run in the fastest manner.

Let me know if this may be infomation that helps you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???