question

HaraldJezek-0525 avatar image
0 Votes"
HaraldJezek-0525 asked ·

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

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 !

sql-server-generaloffice-access-dev
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm quite sure that you can do that, as I think I've seen people talk about this.

But you should re-tag your question with a tag for Access, as this is an Access question. I guess some people over here knows Access as well, but I certainly don't.

0 Votes 0 ·

Thanks for the advise. Tag added

0 Votes 0 ·
AlbertKallal-4360 avatar image
0 Votes"
AlbertKallal-4360 answered ·

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

79749-filterbyform.png

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:

79843-frilebyform2.png

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:

79901-search1.gif

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







filterbyform.png (51.0 KiB)
frilebyform2.png (21.5 KiB)
search1.gif (48.8 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Albert....I sifted through your post and finally found that creating a T-SQL view, linking Access to it and building a query in Access based on this view (I can't use the view directly because on the access side it's a table and not a query) solves my problem.
I'm not a Pro, so there are perhaps better and more professional ways to do it but at least it works and no VBA coding needed.......lol
Thanks for all the info

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered ·

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.

· 9 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Query by form is the official MS term, but I suppose Filter by form is the same.
Pass through queries don't seem to work because I can't find a way to pass the value from the form field to the query (which would be in T-SQL syntax which is different from the Access SQL syntax).
In other words, how do I translate de Access syntax of forms!myform!formfield into a T-SQL syntax ? That's exactly what my original question is.

0 Votes 0 ·
thedbguy avatar image thedbguy HaraldJezek-0525 ·

Re: "In other words, how do I translate de Access syntax of forms!myform!formfield into a T-SQL syntax ?"

You would still use a passthru query, but you would have to update the T-SQL statement using a QueryDef object in your VBA code.

0 Votes 0 ·

getting closer ;-)
Do you have any example how such VBA code should look like ?

0 Votes 0 ·
Show more comments

I am not sure but I think no QueryDef object. Just create a string that is a valid SQL query and put that in the CommandText. So something as in the following.

 objCmd.CommandText = "SELECT * FROM TABLENAME WHERE ID=" & Nz(Me.ID,0)
0 Votes 0 ·
Show more comments
SimpleSamples avatar image
0 Votes"
SimpleSamples answered ·

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
· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, but I think that's not waht I amlooking for.
I simply need a way to pass the input into an unbound formfield to a SQL Server query.
A simple example:
Let's say I want to run a SQL Server query filtering a list of names for a particular name that is put into a text field in an access form.
This process is easy in Access, as I mentioned in my previous message, just putting the filter criteria "forms!myform!formfield into the proper column in the access query.
Now, instead using an access based query, I'd like to use a SQL server based query doing the exact same thing.
1) is that even possible ?
2) if possible, how can it be done ?
Thanks

0 Votes 0 ·
thedbguy avatar image thedbguy HaraldJezek-0525 ·

Hi. I think SimpleSamples sort of gave you the answer already. I think you just need to update this part to include your parameter.

  objCmd.CommandText = "SELECT Stores.Name, Stores.City, Stores.Zip FROM Stores"

For example:

  objCmd.CommandText = "SELECT Stores.Name, Stores.City, Stores.Zip FROM Stores WHERE ID=" & Nz(Me.ID,0)

Hope that helps...

1 Vote 1 ·

Thanks to both of you @SimpleSamples .......I'll give it a try and see if I can make it work.

0 Votes 0 ·

The part that will take time for me to remember how to do is how to get the Recordset into the form. The last time I did something like that was more than 10 years ago.

0 Votes 0 ·
Show more comments
HaraldJezek-0525 avatar image
0 Votes"
HaraldJezek-0525 answered ·

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 ?

· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am not familiar with InfoPath but the best front-end would be C# and Entity Framework (that is what I do and that is why my Access experience is archaic). I know, you do not want to learn that but after investing the time to learn you will be able to quickly create powerful applications. And the knowledge can be applied to ASP.Net (website) applications and can be used for other databases such as SQLite and MySQL. Well I suppose you could transition by initially using VB.Net (ignore the link, I did not make that a link) with Entity Framework. After transitioning you will be excited swimming in the vast ocean.

0 Votes 0 ·

Actually I'm planning to learn C# but that's obviously something that takes time.
Btw, I'm just doing that for my own needs and I'm not a DB or otherwise IT pro. Just trying to keep my brain busy, learning new stuff. That's why my questions probably sound sort of dumb to you guys.....lol

0 Votes 0 ·

Not dumb, but I hope you do not mind me saying unfocused (undecided).

0 Votes 0 ·
Show more comments

1) in my case the SQL server and Access reside on the same machine, so some of issues you mentioned might not exist.

They still do, because SQL Server is designed from the mindset that Albert discussed. And while they are on the same machine, they are tow different processes.

0 Votes 0 ·