query by form with Access as front end and SQL server as back end

Question

I was wondering if it's possible to run a query by form with the form residing in access and the query on SQL server. I'm using Access as front end and SQL server as back end. In other words, how do I translate the forms!myform!formfield into SQL server syntax (if possible at all) ? Btw, I know I could run the query on Access, but I find it easier to write SQL code in SQL server than Access. Thanks !

Answer 1

Ah ok, so the issue not really query by form (that is using a form in access, and doing this:

Once you do above, then a existing data form becomes a template for entering search criteria against the database. And in fact, most of the text boxes not only allow you to enter criteria, but you can even use the drop downs like this:

So this kind of filter by form, or even in some circles filter by example? That works in Access, and that works against SQL server.

You not looking to use the filter by form feature, you looking to use forms!someForm!SomeField in expresisosn. That is a mount everest whopper of a different issue.

Since the UI interface is on your desktop? Then the data server on the other side can't reach out to your computer and pull those expression - no more then when shopping for a book on Amazon, can their database server pull values. In fact, maybe the device is a smartphone - or a web site, or whatever. The database server has no abilities to "know" what the system on the other end is - and it can't reach out and get these values either. (that's a good thing, since while visiting a site with cute cat pictures, I don't want that system rummaging around on my computer). Regardless, this of course now is a architecture issue.
Those database servers have no awareness of your client side system being used - be is a smart phone, web site, or in this case MS-Access - in all of these cases, the database server job is to execute SQL tossed at the database - but the UI parts are things that such server based systems have no abilities to be aware of that client side system.

In fact, SQL server can't tell the difference between some web site, a phone or MS-Access - it only can receive sql commands - not commands that have particular knowledge about the client device or system asking for that information.

Ok, so now that is clear? The issue then is that forms! expression.

Well, there are several ways to do this. You can write your server side sql, save it as a view, link from Access, and then built a Access query against that view with the forms! expressions.

But the REAL answer? Don't use forms! expressions in those queries.

they are easy to use when you start out using Access. But over time, as you grow in skills, you find them to be more hinderances then helps to you.

The first reason?
Well, now that query can't work unless some form is open. In other words, you just destroyed that nice SQL, and now VBA code, forms, query builder, reports will ALL NOT WORK because you have some UI part into the sql part. In effect, this marriage means you can't re-use that SQL anywhere else, and worse the SQL will not work unless some form is open.

And even worse worse? You now hard coded the parameter into that sql. What if you want/need a different criteria? What happens if you working with a different form and say report combination? (you can't, because that simple SQL is now damaged, and attached to one form.

So you are free to write any sql query in SQL studio. Save it and then in Access link to that with a view. And once again you can start using forms!Expresions in that sql - but as i just demonstrated - you REALLY (but really really) do not want to adopt this as a wide spread approach in your software. I could go on about how we need to separate UI and code/data, but that's really not all that great (because then I better offer you an alterative here).

So as you start to move to a server based database, then of course this causes a farther and farther distance from your UI - and I don't mean physical distance, but that of the server system having less and less knowledge about the other end (that Android phone, web site, VB.net or Access system requesting that data). I should point out that from vb.net, C#, Android phones, web sites? None of these systems have that Access ability to have a client side expression exist and be injected right into the SQL.

Access allows this because it allows people without much knowledge to get something done - but as your application grows in complexity, then you want to more and more avoid this.

As noted, one good reason is that the SQL now becomes dependent on that one form - and thus you can't re-use that SQL. the other big reason of course is server side databases also can't use such expression either (since they don't know about the client side - it might be that web site, or a Android phone - not necessary ms-access asking for that data).

However, thankfully most systems have provisions for achieving this goal (of filter or restricting data from the server side system). As I pointed out - the REAL query by form feature in Access does work against SQL server (the above screen shots). In fact I have tested this against a linked table with 2 million rows in the table - and it worked very well.

However, often the issue is not a query by form in which you enter some values into each text box, but in fact the form is data bound and has data in it, and you need that value for the query!!! (again, VERY big difference then query by form).

There are several way to approach this. As noted, work HARD to avoid forms! expressions directly in the SQL. While quick and easy - it quite much destroly the efforts and time you taken to build and write that sql - it can't be re-used.

But the REAL question (as always), is when/where/why do you need this criteria? And 9 out of 10 times? its to launch another form, or to launch another report.

Thankfully, access has a really fantastic feature for this, and it works against any database system, and it even performs well!!!

So, what you use is the WHERE clause of the open form, or the open report command. Not only does this work well, is not really much more any effort then forms! expresisons, but it occures at runtime as opposed to design time! That means you not hard coded the critera into the sql, and BETTER yet is you can change, add, have more, or less or even differnt critera changed - all without having to re-write or modify that sql you write. And it don't matter if you write the sql for client side or for server side!!!

So, say I am on a form, may be it is a search form, or a form in which you enter say some invoice number. Now, you need to run that next form (or open a report based on that?

You do this:

docmd.OpenForm "frmViewInvoice",,,"InvoiceNumber = " & me!InvoiceNumber

Now, the above of course assumes the current form, but you could write it like this:

docmd.OpenForm "frmViewInvoice",,,"InvoiceNumber = " & forms!frmWhatInvoiceForm!InvoiceNumber

And in fact you can EVEN DO this 3rd one - and

docmd.OpenForm "frmViewInvoice",,,"InvoiceNumber = forms!frmWhatInvoiceForm!InvoiceNumber"

The above uses what we call the expression service in Access. Note that the WHOLE string is passed. I tend to NOT do this, and as a habit I use this then 2nd approach (becuased then that works in code.

So, what about the sql you have? Well, it is assume in the above, that you wrote the sql - even on SQ server, saved it as a view (but without ANY sql forms! expressions - well it sql server side - has to be that way anyway!!!).

So, you write the sql and save it server side - its now a sql view. A really typical example would be some reports. You now link to that view from Acess, and you are 100% FREE to add/have/change/slice and dice ANY valid where clause for that openReport (or as noted openReport).

And sql server will respect, and accept those parmaters and optional crtiera aginast that 100% pure T-SQL code you write.

So both forms reports have this magic thing (the where clause), and this is how you can work against server side systems, but not break the bank so to speak in have to write specialized store procedures and other things to accept a set of parameters.

Now, to be fair, at first this does mean you have to drift away from those "really easy" forms! expressions in your code and SQL queries. But the flip side?
You can change that criteria - not have to change the sql query.
You can re-use that saved query all over the place - not married to one form.
You can do this against server side sql you written (and of course saved as views) with reports/forms.
You can add more, or easy have a "all results" option in your UI - and again since the SQL has no such pre-defined criteria, then you can with ease say have things like this:

So notice that I can type in last name, or first name. Then I have things for This seasons, or all seasons.

I am able to do this because I never put those hard coded expressions right into the sql, and thus everything is "on the fly" from me as a developer in terms of the UI I present for such examples.

About the only time I have to use forms! expressions directlly in the SQL is in some cases where I want to say use the Access built in features say to export some data as a csv. So "some" of these built in features in Access are handly, and I would not as a developer write my own CSV export routine when Access has one I can execute with one line of VBA code.

But, for forms opening, reports opening, in most cases, you can use the where clause. You can even use forms! expresisosn in those wehre clauses - but I as a general rule avoid that (since then code I write in VBA can work without forms! having to be opened). But for starting out - I would simply move the forms! expressions from the sql, and move them to the where clause of the openform command.

And in some cases? Yes, you might have to write a few lines of VBA, but if you plop that expression from the SQL sourrouend with quotes into that where clause feature/option of the open form command, then it does work - and works without you have to code up some VBA to make this work.

So, all in all? I am quite much suggesting that if you going to use a server side database system with Access, then you simply have to drop and avoid and not place nasty forms! expressions in the SQL. But, as above shows, you don't have to with much cost at all. it is a tiny bit more effort, but this means then you adopted a design pattern that eliminates the forms! expressions from the SQL, and when you doing client to server systems, you have no choice in this matter.

but, thankfully Access has some great provisions for achieving goal, and you find it little if any efforts to use forms! criteria against forms or reports that are based on data sources that exist on sql server.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Not sure I completely follow what you're asking. I am not quite sure what a "query by form" is. Are you maybe referring to "filter by form?"

Also, if you're using T-SQL in your queries, you won't be able to run them in Access. The most you can do is use passthru queries in Access to let SQL Server run your queries for Access.

Answer 3

I am not sure I understand. You say query on SQL server and that sounds like you want to execute a stored procedure but that probably is not what you mean.

It has been a long time since I have done Access macros. So I am not sure of the details of how to put the following into a form, but the following is an Access macro that can execute a SQL query against a table in the Access database. The output is in the Immediate window.

Public Function TestQuery()
    Dim CommandText As String
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    objCmd.CommandType = adCmdText
    objCmd.CommandText = "SELECT Stores.Name, Stores.City, Stores.Zip FROM Stores"
    objCmd.ActiveConnection = CurrentProject.Connection
    Set objRs = objCmd.Execute
    Debug.Print "------"
    Do While Not objRs.EOF
        Debug.Print objRs(0) & vbTab & objRs(1) & vbTab; objRs(2)
        objRs.MoveNext
    Loop
    Debug.Print "------"

    objRs.Close
    Set objRs = Nothing
    Set objCmd = Nothing
End Function

Answer 4

Thanks for this long answer ;-)
It will take me some time going through that, but just 2 quick comments/questions:

1. in my case the SQL server and Access reside on the same machine, so some of issues you mentioned might not exist.
2. what is the best Frontend when using SQL server ? I'm using Access, because I already know it, but I understand that InfoPath would be another option. Any recommendation ?

Answer 5

I think this page from the Microsoft documentation will help. As noted in another answer, you will have to use ADO to assemble the connection string and query. Once you have the correct cmd.Properties to make the connection for your environment, my understanding is that you just need to build the TSQL statement (using the Sql Server syntax, not Access) and pass the query name and strSQL. Since you are building the string in VBA, you can include the form field as you would for a DAO query, something like:

strSql = "SELECT * FROM dbo.MyTable WHERE MyField = '" & forms!myform!formfield & "';"  

You will need an event on the form to run the VBA code, e.g. in the after update event for formfield.

An answer to a stackoverflow question indicates that you return a recordset through:

Dim rst as ADODB.rst  
  
' BIND RESULTS TO RECORDSET  
Set rst = cmd.Execute