CODEWITHSUNDEEP
sqlsql-serversql-server-2008ms-access
Hasib Ibradzic
Hasib Ibradzic

Reputation: 39

How do I view code written in built in Find function in Access/SQL server?

I am using an Access front end, with a SQL server back end. I have a search button that when clicked executes a command that calls the Find function. When I try to search a field in an Access form, the wrong field is being searched in SQL. For example, if I do a find 40 for the EmployeeID field I get records were EmployeeAge = 40, but not EmployeeID. How can I see what code gets executed when I use the built in find function?

Upvotes: 0

Views: 298

Answers (2)

Hasib Ibradzic
Hasib Ibradzic

Reputation: 39

Thank you for your help. The problem ended up being the code behind the Find button on the form. For some reason it was searching against the wrong column in the database. Since that code is auto-generated when you make the button, I just changed it to specify which column I wanted to use. I'm not sure how this problem came about, because it has been working for years. Only in some instances did it fail. However, with that slight code change everything seems to be working now.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

I suspect you are confusing how the find option works as opposed to the SQL commands access sends to SQL server to grab the records.

You can certainly fire up the SQL profiler and watch/view the SQL commands that Access sends to SQL server to retrieve the records.

The problem of course is that the find option ONLY works on a form where all the records have ALREADY been downloaded from SQL server. In other words, use of the find option assumes that all SQL commands ALREADY have been sent to sql server and THEN the forms records are searched. This means if you use Oracle, SQL Server, or even a plain Jane Access application (local file based), then no underlying SQL commands behind the scenes are send to the SQL server in question WHEN using find option.

As a result, there really not useful information or SQL commands that you can view when using the find option. In fact this explains why using the find option is a poor choice since it assumes that you downloaded all the records into the form BEFORE you try using find.

The SQL commands used to load up the forms data and grab the records can most certainly be viewed. However the find command is internal software to Access and there are no SQL commands etc. to view.

The above suggests in most cases that in place of a find command, you simply set the forms record source like this:

strInvoiceNumber = InputBox("Enter Invoice Number")
strSQL = "select * from tblCustomers where InvoiceNum = " & strInvoiceNumber
Me.RecordSource = strSQL

The above thus means you can see the underlying SQL code, and ALSO means you don’t download 1000’s of records to the form first and THEN search (as this is very slow, and why download large number of records and THEN search - best to ask the user, and then ONLY download the ONE record in question). The above only downloads the one record you search for or requested.

Upvotes: 1

Related Questions