Avatar of edaj6
edaj6Flag for Denmark asked on

Odbc linked tables - search slow.


In my accDB with tables linked to sql server I have a form where recordsource is a table with +60K records. When I use access "build in search" it is very slow (+60sec). In ADP the result is < 3sec.

Is ODBC/Linked tables not using table indexes, is there any way to speed up search?

I have tried creating custom vba search as below, its working but is really slow too.

Set rs = frm.Recordset.Clone
rs.FindFirst "[" & strSearchField & "] = '" & strSearchFor & "'"
frm.Bookmark = rs.Bookmark

Also when searching directly in tables linked tables are much slower than adp. Is this by design or could I be doing something wrong?

Microsoft Access

Avatar of undefined
Last Comment
Kelvin Sparks

8/22/2022 - Mon
Kelvin Sparks

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.



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
Kelvin Sparks

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.

Your help has saved me hundreds of hours of internet surfing.

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.

Kelvin Sparks

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

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 Sparks

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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
Gustav Brock

Using Clone is not fast. Use RecordsetClone:

Set rs = frm.RecordsetClone


Clone is not a problem,

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Kelvin Sparks

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

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

Kelvin Sparks

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.