Lately I've been experimenting a lot with writing dynamic SQL for queries. Even though I'm starting to see some limitations, I'm pretty sure I like this approach a good deal . Today I'll just sketch out some basics and come back in future blogs to talk about the details, including the places where I think it makes a lot of sense--and where it looks like it might not ever really work.
You Want HOW MANY RECORDS?
I got started down this path a few years ago, working as a sub-contractor on a project which was, in fact, just about my first experience using SQL Server as a back-end to Access. The people I was working for had coded up a lot of their forms that way. The rationale, as they explained it, was that they could create very tight SQL statements that way and still pass them to the server as pass-thrus. Returning one record, or a very small number of records, from the server is very desirable in any environment, but especially so here. Their back-end tables contained potentially thousands of parts used in creating a bid for an installation. They needed users with laptops to be able to connect to a remote server and pull down the required records from remote locations, and they didn't often have big fat pipes to pull that data through. Their solution was dynamic SQL in pass-thru queries.
Here's the point. With a standard parameterized Access query, the criteria is usually a control on a form:
SELECT ProductID, ProductName
FROM tblProduct
WHERE tblProduct.ProductID = Forms!frmBidPackage.cboSelectProduct;
That works with local Access tables, and even works with ODBC linked tables. However, you can't pass that SQL String to the server as a pass-thru because the Server has no idea what you mean by "Forms!frmBidPackage.cboSelectProduct"
Their solution--and a very clever one, IMO--was to rewrite the SQL in the pass-thru before sending it to the server/
Here's a cut down of some VBA that might do this:
Private Sub cboSelectProduct_AfterUpdate()
Dim strSQL As String
Dim intProductID as Integer
Dim qDef As DAO.QueryDef
Set qDef = CurrentDb.QueryDefs("qsptProductPriceDiscounts_src")
qDef.SQL = "SELECT ProductID, ProductPrice, Discount
FROM tblProductPriceList
WHERE tblProductPriceList.ProductID = " & Me.cboSelectProduct
Me.cboProductDiscount.RowSource= qDef.Name
End Sub
This, you may think, looks a lot like a typical cascading combo box technique where the "downstream" combo gets a filtered rowsource from the upstream combo. Here, we're using a pass-thru to get that filtered rowsource for the downstream combo.
The resulting SQL string might look like this:
"SELECT ProductID, ProductPrice, Discount FROM tblProductPriceList WHERE tblProductPriceList.ProductID = 1234"
That SQL, SQL Server can handle. If the Row Source for the upstream combo box is limited to valid productIDs, you can also be fairly certain the ProducID passed is going to return a record.
There's another important thing to note in this example, in addition to the fact that it allows me to write the SQL in a pass-thru and send it on to the server in order to get back ONE record, or a very small number of record.
In this VBA module, I was able to substitute the Keyword "Me" for the fully qualified object name that would have been required in a saved query. In a later blog I'll come back to this point and tell you how I was able to leverage that fact for another cool function: Forms that can server equally well as stand-alone forms and as subforms.
So, from that experience, working with pass-thrus, I began to look at all of my other parameterized queries in pure Access solutions. And it turns out that a significant percentage of my parameterized queries could be dealt with in exactly this same way. Of course, for many of them, the original motivation didn't apply--pure Access solutions don't need to worry about the forms references. Nonetheless, I soon discovered another advantage of using this technique: Forms which can be designed to work as either subforms or standalone forms. I'll talk more about that next time.
Mamas, Don't Let Your Babies Grow Up to Be Cowboys
I really need to let it go, but this week, well, I'm just not there yet. Sorry.
Another one for the lady who gave me the best years of my life.
"Cowboys like smoky old pool rooms and clear mountain mornings.
Little warm puppies and children and girls of the night
And them that don't know him won't like him,
And them that do sometimes won't know how to take him,
He ain't wrong he's just different,
but his pride won't let him do things to make you think he's right."
