Efficient way to replace find loan in Access with SQL Server back-end

Question

I have an access front-end user form for data entry that is linked to a SQL Server back-end with an ODBC connection. Users are currently using the 'Find Record' function in Access to look up data records, but as the number of records in the table increases this process becomes rather slow. I know there is a much more efficient way to look up a record, I believe by having SQL Server do the work rather than Access, but I haven't been able to find anything describing the best way. Thanks!

Answer 1

Of course executing some filter on a form is TOO LATE, since you can ONLY filter what the form has ALREADY loaded!

The best approach here is to go to a bus stop and ask a 90 year old lady for advice. Her answer will give you the solution and answer to this problem.

So let’s first ask that 90 year old lady the following question:

When you using an instant teller machine, does it make sense for the instant teller machine to download ALL accounts and THEN ask you for what account to work on?

The old lady will answer: That is really stupid – you want to ask what account to work on then THEN download the one account!

The same goes for when you use Google to search the internet – you don’t download the WHOLE internet into your computer and then use ctrl-f to search everything downloaded into that web browser.

So in the case of Access, if you use a form filter, or use ctrl-F, then you can ONLY search what the form has download from SQL server already. Since old ladies at bus stops will tell you this is crazy, and if you ever used Google, or just about any other software, the KEY CONCEPT is to FIRST ASK what the user wants to search for AND THEN DOWNLOAD the results based on that request.

So don’t download ALL or even ANY records into the form used for searching until such time you KNOW what to search for!

The form should in fact start out un-bound (without a data source). You then simple place a text box near the top of the form, and allow the user to type in say the last name (or just the first few characters of that last name). You can then display the search results in say a continues form that looks like this access form:

Note how in the above the user typed in “Smi” (was looking for smiths).

So the code in the after update even of that text box would then be this:

dim strSQL   as string

strSQL = "select * from tblCustomer where LastName Like "'" & me.txtLastName & "*'"
me.RecordSource = strSQL

So you just stuff right into the form "data source" the SQL with the criteria of the users search. In this case, then if that database has 1 million rows, or 10 million rows then ONLY the requested records are pulled. Such searches will typical occur in LESS than one second – even for large tables with millions of rows.

So don’t use a filter – since that can only filter on existing records you downloaded into that form. And don’t use ctrl-f for searching, as that again can ONLY search records that are already downloaded into the form. However in the above form, ctrl-f (the built in Access search) is available for searching though the 50 or so matches based on smith searching - so we can drill down and jump to say a given first name after we pulled the batch of records from SQL server.

So simply form the SQL each time for the search from scratch and STUFF that SQL into the forms recordsource – the result is access will ONLY pull those records from SQL server – and it going to occur rather fast – even for large tables. (It is of course assumed that you have common columns indexed for rapid searching)

As you can see in above Access example, the search has occurred and a “list” of results is displayed. In above we also provide a “edit” button to edit the one record. So in above the user can click on that button and we open the edit form to edit "details" of that record. As long as we use the forms “where clause” then that form ONLY loads the one record and thus again the launching of that form to edit the one record will occur instant – even for tables of 1 or 5 million rows.

Note that this edit form can be bound directly to the huge linked table you have. The “where” clause will restrict the data pulled for you and ONLY pull the one record despite that form being bound to the large table.

Thus, the follow code to launch the form with only 1 record loaded as per above behind the “glasses” button to edit the one record looks like:

 dim strWhere   as String

 strWhere = "ID = " & me!ID

 docmd.OpenForm "frmCustomers",,,strWhere

Answer 2

SQL pass-through queries are used to send commands directly to SQL Server. By using an SQL pass-through query, you work directly with the server tables instead of having the Microsoft Jet database engine process the data.

Here is a link to a Microsoft article showing How to create a SQL pass-through query in Access: https://support.microsoft.com/en-us/kb/303968

I hope this helps.