DoCmd.GoTo, , acFirst performance issue

[GreenFella]

Hi folks.

I honestly wasn't sure which forum to put this question in. Has anyone else run into this problem.

Two days ago, while adding a couple of new fields to a form, my application started exhibiting real performance issues. These fields were basically a check box and a text field that would auto populate when the box was checked.

Suddenly, navigating from any record in the record set back to the first record takes approximately two full minutes to execute.

The recordset is fairly large and the form that I am working with is a farily complex one with any number of subforms and the likes, but I have never had an issue with it and it has been running in our production environment without issue for years.

This is an Access 2003 application linked via ODBC to a SQL Server backend. I am running on a Windows XP Pro desktop.

Navigating from record to record is no issue, back or forward. Navigating from anywhere in the recordset to the last record is not an issue. But, if I try to navigate from anywhere within the recordset to the first record... well, let's just say I have some time on my hands.

Here is the code:

Private Sub cmdFirstCustomer_Click()
On Error GoTo FirstCustomerClick_Error

Dim dtStart As Date
Dim dtEnd As Date

dtStart = Now()

DoCmd.GoToRecord , , acFirst

dtEnd = Now()

MsgBox "Start: " & dtStart & " End: " & dtEnd

ESICustName.SetFocus

Exit Sub
FirstCustomerClick_Exit:
Exit Sub

FirstCustomerClick_Error:
MsgBox Err.Description
Resume FirstCustomerClick_Exit

End Sub

The MsgBox gives me the start and end time. And it is consistently two minutes, give or take a second.

Has anyone come across this issue in the past? Does anyone have any thoughts regarding this. I must admit that I have pretty much exhausted all options that I can think of, including Compact & Repair and copying all objects into a brand new database.

I am stumped.

Thanks In Advance
Greenfella

 

[MajP]

Any chance you could use one of "move" method of the forms "recordset"?

 

[TheAceMan1]

How are ya GreenFella . . .

[blue]   Me.Recordset.MoveFirst[/blue]

See Ya! . . . . . .

 

[GreenFella]

Thanks for the suggestion but it didn't work. Still takes two minutes to track back to the first record.