Executing 'Transform' SQL in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi -

I have a crosstab query that I am trying to execute in the Visual Basic
environment. The SQL statement starts with a 'TRANSFORM' command and I get an
error that says:

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'"

Why is it that Visual Basic does not seem to recognize the TRANSFORM
(CrossTab) SQL statement?

I would appreciate it if you could provide me with some advise on how to
execute 'TRANSFORM' SQL statements in VB.

Thanks much!
 
Hi:
It works for me. Try this --

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblTest_Crosstab", CurrentProject.Connection, adOpenForwardOnly

Here tblTest_Crosstab is a Crosstab query object.


Regards,

Naresh Nichani
Microsoft Access MVP
 
Are you executing this query against a Jet (MDB) database? Not SQL Server or
MSDE? TRANSFROM is a proprietary feature of Jet, and will not work with any
current release of SQL Server.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Hi Naresh I still see the same error

Naresh Nichani MVP said:
Hi:
It works for me. Try this --

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblTest_Crosstab", CurrentProject.Connection, adOpenForwardOnly

Here tblTest_Crosstab is a Crosstab query object.


Regards,

Naresh Nichani
Microsoft Access MVP
Click to expand...
 
Yes. I am using a MDB database. I tried Naresh's recommendation and i still
see the same error. Does this mean there is absolutely no way of running a
'transform' statement in VBA??

I am lookin to write an SQL statement which has parameters that changes
according to whether certain combo boxes have been selected. So for example,
in one case I may have parameter A used, and in other cases I may have
parameters B,C used instead. Thus, the crosstab SQL query changes in length.
Is this not possible to do in code??
 
Hi Leo,

There should be a way of doing this, but I don't have time to experiment with it at the moment.
Crosstab queries require that parameters be explicitly defined (see
http://support.microsoft.com/?id=209778 ). This means that you'll need to look up the method for
setting the parameters in VBA code prior to running the query. I believe you will need to save
the SQL statement first as a new query object, then use code to modify this query to add the
parameters, and finally use code to execute the saved querydef.

Try using Debug.Print in the VBA editor to print your completed SQL statement to the Immediate
window. Then copy this statement and paste it into a new query. I think you'll find that in order
for it to run correctly, you'll need to follow the steps in the above referenced KB article. If a
SQL statement printed to the Immediate window and pasted into a new query will not run correctly,
when run as a query, then it certainly will not run when executed directly in code.

Tom
_________________________________________

Yes. I am using a MDB database. I tried Naresh's recommendation and i still
see the same error. Does this mean there is absolutely no way of running a
'transform' statement in VBA??

I am lookin to write an SQL statement which has parameters that changes
according to whether certain combo boxes have been selected. So for example,
in one case I may have parameter A used, and in other cases I may have
parameters B,C used instead. Thus, the crosstab SQL query changes in length.
Is this not possible to do in code??
 
As Tom indicates elsewhere in this thread, it seems the problem is the
parameters. To set parameters via VBA, you can use the Parameters collection
of the QueryDef object ...

Dim qdf As QueryDef
Set qdf = "NameOfQuery"
qdf.Parameters("NameOfParameter") = ValueOfParameter

Now you can open a recordset using the OpenRecordset method of the QueryDef
instead of the more commonly used OpenRecordset method of a Database object
....

Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset

Obviously, as Tom says, this means you will have to save the SQL statement
as a saved query. If you're building the SQL statement dynamically, you can
still do that by manipulating the SQL property of the saved query ...

Set qdf = "NameOfQuery"
qdf.SQL = "your SQL statement here"
qdf.Parameters("NameOfParameter")=ValueOfParameter

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Hi Tom and Brendan -

The problem I am facing is not in the parameters. My "qdf.parameter"
statements seem to be working fine in VBA.

However, what I am trying to do is to write a code that allows the user to
execute the SQL statement based on what is selected in my combo boxes. So for
example, I have 4 combo boxes (Region, Country, Company, Division), and I
want the user to have the flexibility to select any (1 or more) of the combo
boxes.

Thus, I know I have to write a "qdf.parameters" statement to define the
parameters in each of the 4 combo boxes. But what I don't know is how to
write a statement to account for combo boxes that were not selected (i.e.
have NULL or "" values in them).

I tried the following but it returns me nothing in my query when a combo box
is not selected.

If forms!frm_Reports!cmb_Region <> "" Then

qdf.parameters("forms!frm_Reports!cmb_Region") = forms!frm_Reports!cmb_Region

Else

qdf.parameters("forms!frm_Reports!cmb_Region") = "Like ""*"" "

End If

This doesn't seem to work. Please advise!
Thanks so much!
 
Hi Tom and Brendan -

The problem I am facing is not in the parameters. My "qdf.parameter"
statements seem to be working fine in VBA.

However, what I am trying to do is to write a code that allows the user to
execute the SQL statement based on what is selected in my combo boxes. So for
example, I have 4 combo boxes (Region, Country, Company, Division), and I
want the user to have the flexibility to select any (1 or more) of the combo
boxes.

Thus, I know I have to write a "qdf.parameters" statement to define the
parameters in each of the 4 combo boxes. But what I don't know is how to
write a statement to account for combo boxes that were not selected (i.e.
have NULL or "" values in them).

I tried the following but it returns me nothing in my query when a combo box
is not selected.

If forms!frm_Reports!cmb_Region <> "" Then

qdf.parameters("forms!frm_Reports!cmb_Region") = forms!frm_Reports!cmb_Region

Else

qdf.parameters("forms!frm_Reports!cmb_Region") = "Like ""*"" "

End If

This doesn't seem to work. Please advise!
Thanks so much!
 
Hi Tom and Brendan -

The problem I am facing is not in the parameters. My "qdf.parameter"
statements seem to be working fine in VBA.

However, what I am trying to do is to write a code that allows the user to
execute the SQL statement based on what is selected in my combo boxes. So for
example, I have 4 combo boxes (Region, Country, Company, Division), and I
want the user to have the flexibility to select any (1 or more) of the combo
boxes.

Thus, I know I have to write a "qdf.parameters" statement to define the
parameters in each of the 4 combo boxes. But what I don't know is how to
write a statement to account for combo boxes that were not selected (i.e.
have NULL or "" values in them).

I tried the following but it returns me nothing in my query when a combo box
is not selected.

If forms!frm_Reports!cmb_Region <> "" Then

qdf.parameters("forms!frm_Reports!cmb_Region") = forms!frm_Reports!cmb_Region

Else

qdf.parameters("forms!frm_Reports!cmb_Region") = "Like ""*"" "

End If

This doesn't seem to work. Please advise!
Thanks so much!
 
In the example you posted, you are only checking for an empty string, you
are not checking for a Null value. But this is clearly not the actual code,
as it is not capitalized the way VBA would capitalize it, so I can't tell
for sure whether the mistake is in the actual code, or only in the newsgroup
post. In situations like this, it's always a good idea to copy and paste the
actual code rather than retyping it.

If the error is in the actual code, try replacing ControlName <> "" with
Len(Trim$(ControlName & vbNullString)) <> 0. That will catch Null values,
empty strings, and strings consisting only of spaces.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Hi Brendan -

Thanks for the prompt response. I tried your recommmendation and I got a
"Type Mismatch" error.

Here was the code I used:

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region & vbNullString)) <> "" Then

qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") =
Forms!frm_Reports!cmb_Dir_Rep_Region

Else

qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") = "Like ""*"" "

End If

Did I miss something? Please help...
 
Len returns a number: the number of characters in the string.

You need

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region & vbNullString)) > 0 Then
 
Hi Douglas and Brendan -

I changed my code to "If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region &
vbNullString)) > 0"
But my query still returns me a blank query when any of the combo boxes is
blank. This is no different from when I was using "If
Forms!frm_Reports!cmb_Dir_Rep_Region <> "" " in the beginnning.
Is my Like ""*"" statement correct?? How can I write a statement that
'ignores' the parameter completely when 1 combo box is not selected? (this is
what Ive been trying to do)
 
No, your Like ""*"" is not correct. You can't change the operator as part of
a parameter's value.

Try changing your SQL so that it uses Like with each of the parameters, and
then use

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region & vbNullString)) <> 0 Then
qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") =
Forms!frm_Reports!cmb_Dir_Rep_Region
Else
qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") = "*"
End If

(when you don't provide a wildcard character, Like is the same as =)

Alternatively, don't use parameters at all, and change your SQL to something
like:

WHERE Field1 = Forms!frm_Reports!cmb_Dir_Rep_Region OR
Forms!frm_Reports!cmb_Dir_Rep_Region IS NULL
 
Hi Doug -

I tried the "*" but I get the same results as when I started (i.e. nothing
returned in my query). I did not quite understand what you mean by modifying
the SQL statement so that it uses 'Like' with each of the parameters.

Also, I can't NOT use parameters because since I am writing my code in VBA
(mdb), it doesn't recognize a 'Transform' statement which is bascially a
crosstab query. Thus, I have to 'Open Query' as opposed to having the
flexibility to 'RunSQL'. (If I could run SQL in VBA code in .mdb, then my
life will be so much easier in this problem).

Thanks for all your help. Any more thoughts will be great!!
 
Your SQL is going to look something along the lines of

WHERE Field1 = Forms!frm_Reports!cmb_Dir_Rep_Region

Change each of those = signs to

WHERE Field1 LIKE Forms!frm_Reports!cmb_Dir_Rep_Region
 
Hi Doug -

Sorry for the delay in response. I've been out of the country for the past
week.

Anyway, I can't do your below suggestion because I am unable to execute SQL
in my vba(.mdb) environment. This is due to the 'Transform' part of the
statement that VBA give me a "expected select...." error (i.e. it doesn't
recognize the 'Transform' part of the statement)

If I could execute Transform in my vba (.mdb), then it would be alot easier
for me to manipulate the code..
However, i am stuck with the openquery function..
 
What exactly do you mean by 'execute' in this context, Leo? Generally
speaking, in code, one executes an action query, and opens a recordset on a
select query. A cross-tab query is a form of select query, and I don't have
any difficulty opening a recordset on a cross-tab query ...

Public Sub TestTransform()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

'SQL copied from Quarterly Orders By Product query in Northwind ...
strSQL = "TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS " & _
"ProductAmount SELECT Products.ProductName, Orders.CustomerID,
Year([OrderDate]) AS OrderYear " & _
"FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = " & _
"[Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID WHERE (((Orders.OrderDate) " & _
"Between #1/1/1997# And #12/31/1997#)) GROUP BY
Products.ProductName, Orders.CustomerID, Year([OrderDate]) " & _
"PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In ('Qtr 1','Qtr
2','Qtr 3','Qtr 4');"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Hi Brendan -

I tried the below and I got an "Object Required" error message.

Here is my SQL:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String


' Dim qry_Training_PCT As String

strSQL = "TRANSFORM 1-(Sum(IIf([%
grade]<70,1,0))/Count([tbl_Main_report]![STUDENT_ID])) AS [PCT_Pass] " & _
"SELECT tbl_Main_Report.Region, tbl_Main_Report.Director " & _
"FROM tbl_Main_Report INNER JOIN STUDENT_FIELDS ON
tbl_Main_Report.STUDENT_ID = STUDENT_FIELDS.STUDENT_ID " & _
"GROUP BY tbl_Main_Report.Region, tbl_Main_Report.Director " & _
"PIVOT tbl_Main_Report.COMPETENCY_GROUP"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close




Brendan Reynolds said:
What exactly do you mean by 'execute' in this context, Leo? Generally
speaking, in code, one executes an action query, and opens a recordset on a
select query. A cross-tab query is a form of select query, and I don't have
any difficulty opening a recordset on a cross-tab query ...

Public Sub TestTransform()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

'SQL copied from Quarterly Orders By Product query in Northwind ...
strSQL = "TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS " & _
"ProductAmount SELECT Products.ProductName, Orders.CustomerID,
Year([OrderDate]) AS OrderYear " & _
"FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = " & _
"[Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID WHERE (((Orders.OrderDate) " & _
"Between #1/1/1997# And #12/31/1997#)) GROUP BY
Products.ProductName, Orders.CustomerID, Year([OrderDate]) " & _
"PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In ('Qtr 1','Qtr
2','Qtr 3','Qtr 4');"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Leo said:
Hi Doug -

Sorry for the delay in response. I've been out of the country for the past
week.

Anyway, I can't do your below suggestion because I am unable to execute
SQL
in my vba(.mdb) environment. This is due to the 'Transform' part of the
statement that VBA give me a "expected select...." error (i.e. it doesn't
recognize the 'Transform' part of the statement)

If I could execute Transform in my vba (.mdb), then it would be alot
easier
for me to manipulate the code..
However, i am stuck with the openquery function..
Click to expand...
Click to expand...
 
Back
Top