Single form slow to load for only one table.

[JayF]

Hi all,

I have an Access 2007 database that is struggling with one particular table only. The table is fairly wide at 49 fields, but I see no way to further normalise it.

On opening the main details form for this table, which is sourced directly to the table and not a query, there is a delay of around 3 - 6 seconds between opening the form window (Popup = Yes) and displaying the controls.

A split form (datasheet) that runs primarily from the same table, but via a SELECT query that includes all fields from the table plus a couple of others from elsewhere, has no problems at all.

I have eliminated various possible causes of the delay, but am unable to pinpoint exactly what it is causing it.

I have tried to diagnose the problem by doing the following:

- I have created a form for the table using the form wizard, thus eliminating all VBA/Macro causes. In Popup = No, the status bar at the bottom shows "Calculating... " during the delay. In Popup = Yes, the delay is exactly the same.

- I deleted all controls bar one (text box for ID field) from the form. No change.

- I removed all Lookups from the table design. These were redundant anyway. No change.

I should also note that the delay is inconsistent. I have sat here for a while trying to find a pattern, but there is either a delay or it is instant, even when opening the same record over and over or alternating between two different ones. There is more often a delay than not.

I've now exhausted my own all ideas on how to get to the bottom of this, any thoughts you guys have would be very much appreciated.

Thanks,

Jay

[TheSmileyCoder]

Nothing really springs to mind.

Have you tried changing the forms recordsource to

"Select * FROM  [TableName]"

to see if that has any impact?

This may or may not be related. If you have no open connections to a linked backend (no bound forms or recordsets active) then upon opening any linked table, a connection must first be made. The delay incured when having to first make a connection is quite noticeble and at least for where I am working is of the order of 3-8 seconds. I therefore keep a hidden form open bound to a single record, in order to maintain a persistent connection to the backend.

[JayF]

Hi TSC. Thanks for your suggestion. Changing the recordsource to the SQL statement had no impact.

On the version I am working on all the tables and forms are contained within the same DB. I plan to split them before going live.

While writing this post your post led me down a train of thought...

The only two ways for a user to open the form are from a "New Membership Card" button on the switchboard and via an onclick event macro on the card's ref number in the split form datasheet. Clicking on the New Card button consistently gives NO delay. I'm going to rewrite the macro in VBA, closing the list form before opening the details form. My thinking being that maybe opening two forms sourced to the same table is causing a delay. Is that feasible?

I'll post back shortly.

[JayF]

This fixed it. I guess it must have been the split form in the background causing it.

In case anyone needs it, this is the code I used. There might be a better way of doing it, but this worked for me:

Private Sub Ref_Number_Click()
Dim intID As Integer

intID = Me.[Card ID]

DoCmd.Close acForm, "lfrmCardList"
DoCmd.OpenForm "dfrmCardDetails", , , "[Card ID] = " & intID

End Sub

By the way, interesting point on maintaining a link to a backend, TSC. I think I have inadvertently done this by displaying a logo, that gets its path from a field in tblSettings, on the switchboard. At least one form (including the SB) will always be open, so I guess that should do the trick. Thanks again.