Transform using DoCmd.RunSQL fails

[mls via AccessMonster.com]

Hi, my transform is failing with ERROR 2342 , How can I redo this...
basically I want my "detect" to be the column headers and "values" as rows..
This run fine in query window but I am trying to automate the transform
process

DoCmd.RunSQL "TRANSFORM First(test.value) AS FirstOfct_num SELECT test.Sample,
test.user FROM test GROUP BY test.Sample, test.user PIVOT test.detect;"


data below..
Sample User Detect Value
5442 img Test 1 Undetermined
5442 img Test 2 27.7402
5442 img Test 3 33.0166

Thank you

 

[John Spencer]

You cannot use RunSQL to execute a non-action query. Action queries Change
the data and are UPDATE, INSERT, DELETE queries.

Since I don't know how you intend to use the view of the data generated by the
query string, I can't tell you how to get the results.

Three choices that I can think of right off the bat.

You can create a recordset in VBA if you want to manipulate the information.

You can assign the SQL string as the record source for a form or report

You can set the SQL string as the SQL property of a query and OPEN the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[mls via AccessMonster.com]

Actually I want save these transformed results into a table and do some
manuplations later
I tried the following but could see only sample column in the immediate
window..


Sub Test()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
'Dim qry_Training_PCT As String
strSQL = "TRANSFORM First(test.value) AS FirstOfct_num SELECT test.Sample,
test.user FROM test GROUP BY test.Sample, test.user PIVOT test.detect;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close
End Sub

You cannot use RunSQL to execute a non-action query. Action queries Change
the data and are UPDATE, INSERT, DELETE queries.

Since I don't know how you intend to use the view of the data generated by the
query string, I can't tell you how to get the results.

Three choices that I can think of right off the bat.

You can create a recordset in VBA if you want to manipulate the information.

You can assign the SQL string as the record source for a form or report

You can set the SQL string as the SQL property of a query and OPEN the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, my transform is failing with ERROR 2342 , How can I redo this...
basically I want my "detect" to be the column headers and "values" as rows..

[quoted text clipped - 11 lines]

Thank you

 

[John Spencer]

I'm not sure that this can be done with a crosstab query as the source in vba.

You might have to create a recordset and then populate the table by stepping
through the recordset and grabbing the values.

Alternative would be to create the crosstab query and save that then use the
saved query to build populate your table.

After you create the query def your query string might be

SELECT SavedCrosstab.* INTO MyNewTable
FROM SavedCrosstab

OR if MyNewTable has already been created
INSERT INTO MyNewTable
SELECT * FROM SavedCrosstab

Or
INSERT INTO MyNewTable (Sample, User, Value1, Value2)
SELECT sample, User, NameOfCrosstabColumn1, NameOFCrosstabColumn2
FROM SavedCrosstab

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Actually I want save these transformed results into a table and do some
manuplations later
I tried the following but could see only sample column in the immediate
window..


Sub Test()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
'Dim qry_Training_PCT As String
strSQL = "TRANSFORM First(test.value) AS FirstOfct_num SELECT test.Sample,
test.user FROM test GROUP BY test.Sample, test.user PIVOT test.detect;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close
End Sub

You cannot use RunSQL to execute a non-action query. Action queries Change
the data and are UPDATE, INSERT, DELETE queries.

Since I don't know how you intend to use the view of the data generated by the
query string, I can't tell you how to get the results.

Three choices that I can think of right off the bat.

You can create a recordset in VBA if you want to manipulate the information.

You can assign the SQL string as the record source for a form or report

You can set the SQL string as the SQL property of a query and OPEN the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, my transform is failing with ERROR 2342 , How can I redo this...
basically I want my "detect" to be the column headers and "values" as rows..

[quoted text clipped - 11 lines]

Thank you

 

[mls via AccessMonster.com]

Thank you John. This works perfect

I'm not sure that this can be done with a crosstab query as the source in vba.

You might have to create a recordset and then populate the table by stepping
through the recordset and grabbing the values.

Alternative would be to create the crosstab query and save that then use the
saved query to build populate your table.

After you create the query def your query string might be

SELECT SavedCrosstab.* INTO MyNewTable
FROM SavedCrosstab

OR if MyNewTable has already been created
INSERT INTO MyNewTable
SELECT * FROM SavedCrosstab

Or
INSERT INTO MyNewTable (Sample, User, Value1, Value2)
SELECT sample, User, NameOfCrosstabColumn1, NameOFCrosstabColumn2
FROM SavedCrosstab

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Actually I want save these transformed results into a table and do some
manuplations later

[quoted text clipped - 41 lines]

[quoted text clipped - 11 lines]
Thank you