Runing a Cross Tab query using SQL Statements in VB6

[itmasterw]

Hi,
I am not that knowledgeable with Cross Tab queries, but I have a couple that I created in Access. I am now writing a VB program and want to know how I could write an equivalent cross tab query SQL, in my VB program and insert this into a table in a Access database. Here is my query in Access:

TRANSFORM IIf(IsNull(First(tblMasterMerStsts.Count)),"-",First(tblMasterMerStsts.Count))
SELECT tblMasterMerStsts.Report_ID
FROM tblMasterMerStsts
GROUP BY tblMasterMerStsts.Report_ID
PIVOT tblMasterMerStsts.Date;

But I do not think you can just put this in as a SQL statement in VB.

Is there a way?
Thank you

 

[BiggerBrother]

Check this link

http://ewbi.blogs.com/develops/2005/01/access_sql_tran.html

which has an example of SQL statements to replace the TRANSFORM statement.

BB

 

[ewbi]

Unfortunately, that SQL example won't help with itmasterw with an open ended SQL TRANSFORM. However, there's no reason why you can't use a SQL TRANSFORM statement from a VB program. In fact, the language (VB, VBScript, Access VBA, etc.) isn't relevant. What matters is the database driver you'll be utilizing in your language to execute the query. Assuming you are using JET's native ADO/OLEDB driver, you can certainly open a Recordset based on a TRANSFORM.

Below is a simple VBScript that does just that using the sample Access database I linked to in my post (which was linked to above by BB). It uses the Microsoft.Jet.OLEDB.4.0 driver, but could have used the 3.51 driver if the sample database had been in an older format. Except for the WScript references, this script can be used in VB 6.0 without change. However, you'll probably set a project reference to the ADO library in VB, and so will use enum constant names instead of hardcoded values.

Good luck.

Set connection = WScript.CreateObject("ADODB.Connection")
connection.Provider = "Microsoft.Jet.OLEDB.4.0"  
connection.Properties("Data Source") = "c:\temp\sql.transform.mdb"
Call connection.Open

Set recordset = WScript.CreateObject("ADODB.Recordset")      
Set recordset.ActiveConnection = connection
recordset.CursorLocation = 2 ' server
recordset.CursorType = 0 ' forward-only
recordset.LockType = 1 ' read-only
    
Call recordset.Open("TRANSFORM Sum(mytable.amount) AS total " & _
                    "SELECT mytable.project " & _
                    "FROM mytable " & _
                    "GROUP BY mytable.project " & _
                    "PIVOT mytable.year;", , , , 1)

results = ""
For Each field in recordset.Fields
  results = results & field.Name & vbTab
Next                    
results = results & vbCrLf & recordset.GetString(2)

Call recordset.Close
Call connection.Close

WScript.echo results