Odbc linked tables - search slow.

FindFirst is a slow as you can get. A simple SELECT statement to find a record would be much much faster.

Comparing an accdb with an adp is not wise. They are two very different beasts. The tables you see in an adp are actually SQL Server tables and you are interacting directly with the server (think of it as a windows directly into SQL Server). In an accdb, you have linked tables and the data is "mirrored" into your local tables and so you 60K+ records have to travel from the SQL Server to your PC - they do not in the adp. An adp uses OLEDB to connect, whereas an accdb uses ODBC.

Your example above needs for Access to load ALL records from your table into the PC, then pass through them one by one to find the first match.

Kelvin

Thanks,

But how should the select statement be used, is there a fast way to move to a record if I know the key value? - I dont want to filter the recordsource, since users need to move to records before/after the match

It depends what you are trying to do with the record. Why are you searching for it and what will you do with it when you find it?

The answers to that will determine how you search.


Kelvin

I want to be able to check if record exist and if it does I should be able to edit, view and delete record.

Just as access build in find.

Jakob

OK,
Have a form that is bound to the table containing the records you're checking (you may want to drop the binding later). In the form header place a text box where you'll enter the ID (primary key or other unique field) that you want to search for. In the After Update event of that text box use something like

Dim sSQL as String

sSQL = "SELECT * FROM MyTable WHERE SearchFeild = '" & me.txtsearchbox & "'"

Me.RecordSource = sSQL
Me.Requery


What this does is build a select string that selects the record from your table that you're looking for, it them sets the form's recordsource to that SQL and then requires the form. This will then bind the form to just that record. You might want to put some form of handler in to deal with non existent records. This will return the record in less than a second.

Kelvin

I'm heading home for the night (just coming up to 10 pm here). Be back at 6.30 tomorrow morning and will look for any feedback then.

Good luck

Kelvin

Thanks but this is a filter on the record source. I need to select the record searched for. Also, I would like to have the search code in a seperate form that can be called from multiple forms.

I have found that gotoRecord is really fast. I will try and find a solution with this, something like:

rs.open (select row, id from (select ROW_NUMBER ( ) OVER (..
DoCmd.GoToRecord , , acGoTo, rs.row

Using Clone is not fast. Use RecordsetClone:

Set rs = frm.RecordsetClone

/gustav

Clone is not a problem,

Set rs = frm.Recordset.Clone  < 1sec
Set rs = frm.RecordsetClone < 1 sec
FindFirst "[ID] =" & intID  > 60sec

Jakob

Kelvin, thanks for your comments.

I may use some of your ideas, but for now I have made a custom search form. Its working and gets result in < 0.5 sec

Function GetRow():
strSql = "Select TOP 1 row from " & _
        "(select ROW_NUMBER ( ) OVER (" & strOrderBy & ") as row, " & strSqlSelect & ")qry where " & strSearchField & " like '%" & strFind & "%' AND row > " & lngRowStart

Form:
DoCmd.GoToRecord acDataForm, frm.Name, acGoTo, GetRow

Thanks,
Jakob

Hi Jakob

Ina round about sort of way, your approach and mine have some similaritys. The important thing is that you've dropped that FindFirst which was killing your performance.


Kelvin