Dynamically changing a Pass Through Query by Replacing the SQl

[tcarnahan]

I am helping out an organization who is using a COTS application tied to a SQL-Server 2005 database. The organization is using Access 2007. The SQL Server database is maintained by the company who owns the COTS package. They are very restrictive of the database and only allow ODBC connections and SELECT queries. I cannot create any objects including temp tables. I cannot use Data Access projects.



The query I am using is very long and involves a number of unions and a pivot. The query works fine as a source for my reports, but I need to be able to dynamically change parameters in my WHERE clause. A number of years ago, I remember a technique (which I have forgotten how to use properly). It involved saving a Pass-Through (PT) query with replace-able "tokens" in it that one replaces before running the query. I have several "<token>" markers in my PT. I want to do the following:

strSql = qdf.SQL

strSql = REPLACE(strSql, "<token>", "<my new string")

qdf.SQL = strSql

I am having two problems:

• I don't remember what to do after I replace the SQL in the queryDef to have it used as the source for a local query that uses the PT. The local query is the source for my report.

• The PT SQL is quite long and I keep getting an error when I attempt the qdf.SQL = strSql. It says I need to have a SELECT query. When I capture the SQL by using debug.print, the intermediate window shows that my SQL has been truncated. I don't know if the STRING data type can handle the number of characters (it can't be more than 3,000 chars) OR if the intermediate window is truncating what I am seeing.

Any suggestions on how to proceed with bullet 1? Any suggestions on how to troubleshoot bullet 2?

Thanks ahead of time,

Tom

[tcarnahan]

As a test, I broke up my big query into 4 smaller PT queries and tried to concatenate them (see below). Note: I was wrong. Including spaces, the SQL is about 8,000 characters.

I realize that I probably should just put all 8,000 characters into a big string in VBA and go from there, but that is a lot of code to put quotes and line continuation symbols on, so I tried it this way just to see if I could concatenate the 4 strings. I commented the line where it failed.
This is the error. When I debug.print, I find that the first 6,000 characters are gone.

Private Sub Test_3()

Const cstr_QuerySection_1 As String = "PT_Test_1"
Const cstr_QuerySection_2 As String = "PT_Test_2"
Const cstr_QuerySection_3 As String = "PT_Test_3"
Const cstr_QuerySection_4 As String = "PT_Test_4"

Dim db As DAO.Database

Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim qdf3 As DAO.QueryDef
Dim qdf4 As DAO.QueryDef

Dim qdfMaster As DAO.QueryDef

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

Dim strBigSQL As String

Set db = CurrentDb

Set qdf1 = db.QueryDefs(cstr_QuerySection_1)
Set qdf2 = db.QueryDefs(cstr_QuerySection_2)
Set qdf3 = db.QueryDefs(cstr_QuerySection_3)
Set qdf4 = db.QueryDefs(cstr_QuerySection_4)

strSQL1 = qdf1.SQL
strSQL2 = qdf2.SQL
strSQL3 = qdf3.SQL
strSQL4 = qdf4.SQL


strBigSQL = strSQL1 & strSQL2 & strSQL3 & strSQL4

Set qdfMaster = db.CreateQueryDef("tmpQdf", strBigSQL)  -- ***  FAILS here;  strBiqSql is truncated. ***

qdfMaster.Execute

Note, when I take the original query (all 4 parts together) and open it as follows, it runs fine:

DoCmd.OpenQuery "<orig>"    ' where <orig>  is the original saved query (with 8,000 chars)

End Sub

[ItsMe]

I am not an expert on pass through queries but have you tried to leave the WHERE clause out of the pass through query OBJECT and then dynamically adjust the WHERE criteria via VBA? The Token thing sounds cool but I have never used it before.

Maybe you can call the named pass through query and add your WHERE clause to a new query def. Then delete your new qry def after you are done with it. In other words, grab the big qry by name without the qryDef and then add the new qryDef that you will delete later ,when you are done with it.

Just a thought

[tcarnahan]

I had considered that idea, but because my SQL pivot has to have the names of the fields (which will change as I dynamically manipulate them) in the SELECT and in the PIVOT ... in other words, the query has to have code changed in multiple places, not just in the WHERE clause.

[ItsMe]

Right, you did mention PIVOT. Hmmm

[Dal Jeanis]

1) Consider making a function or procedure that builds the SQL. Pass the tokens into the procedure as string parameters. I understand that it's a lot of trouble to set up the first time, but it is a method with a certain result.

2) Alternately, if you can post your 8000 character query and some sample parameters, I can test with it and see what I can work out.

[tcarnahan]

I could create an MS-Access function to create the SQL, but I cannot create SQL functions or procedures to do so. I'll give it a try ... I am running out of ideas.

I think the problem is that there is a limitation on the length of a VBA string and I am not sure how to get around it.

I found an example of the technique I used before, but because of the possibility of the string data being truncated, I don't know if this will work:

Public Function PassThru_Recordset( _
              strSQL_Server_Code As String) As DAO.Recordset
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function swaps out the code of the "placeholder" pass thru query and
' replaces it with SQL passed from the calling procedure. Then it runs it.
' The constant "gcstr_Qry_PT_GeneralPurpose" is the name of a saved generic PassThru query
' that has a valid connection string whose code we will dynamically change.
' before we run the query.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    
    Set qdf = db.QueryDefs(gcstr_Qry_PT_GeneralPurpose)
    
    'Swap out the code in the GP PassThru query:
    qdf.SQL = Trim(strSQL_Server_Code)     ' This is where it would fail.
    
    qdf.ReturnsRecords = True
    
    db.QueryDefs.Refresh
    
    Set PassThru_Recordset = qdf.OpenRecordset
    
    'Clean up:
    Set qdf = Nothing
    Set db = Nothing

End Function

In this case, I open a recordset to process rows one by one, however, what I need is
to permanently alter the query and then allow local Access queries to join on 'it.

[Dal Jeanis]

I'm sensing that there may be an underlying "trying to do everything at the same time" concept that's making your life impossible.

I don't exactly understand how you can be limited from creating queries and yet you can build an arbitrary query to any arbitrary specification. If you can do that, then you can build literally ANY query, and rules be damned.

1) So, what's the real political situation? Who says you CAN'T, who says you MUST, and what do the other guys say you CAN?

2) Is it possible that you can break down your requirements into a couple of distinct sets?

[ItsMe]

The query works fine as a source for my reports, but I need to be able to dynamically change parameters in my WHERE clause

Maybe you can post the SQL that works to start. Then describe what you need to add to it.

[June7]

And why can't you create temp tables in the Access frontend? Why can't you fill them with records from the SQL tables?

[tcarnahan]

I started replacing the query that works with the blocks of code that I am trying to replace and the string variable truncates at around 6 or 7,000 characters. I tried shortening the query and it works. I didn't know that there was a limit on VBA strings.

[tcarnahan]

I meant I can't create temp tables in the SQL Server backend ... the COTS owner restricts everything but SELECT statements. I can create temp tables in the front-end, but that doesn't help me. In the front-end, I join several local tables with the results of the Pass-Through query.

[June7]

Glad you found a solution. I am surprised by the limit. An Access query object is supposed to allow 64,000 characters it its SQL statement. Don't know why that would be different with the VBA variable. The max length of value in string variable is supposed to be 65,400 (fixed) to 2GB (variable) depending on how the variable is declared. http://www.cpearson.com/excel/SizeString.htm

[tcarnahan]

I was surprised too. The only way I found out was by trial and error.

[ItsMe]

Maybe the limit has to do with the ODBC drivers or some other factor from outside of Access. Interesting though.