Avatar of edaj6
edaj6Flag for Denmark asked on

Odbc linked tables - search slow.

Hi,

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?

Thanks,
Jakob
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.

Kelvin
ASKER
edaj6

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
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.


Kelvin
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
edaj6

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

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

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

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

/gustav
ASKER
edaj6

Clone is not a problem,

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

Jakob
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.
rwheeler23
ASKER CERTIFIED SOLUTION
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
or
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
ASKER
edaj6

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
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.


Kelvin