Access 2003 Transform Queries??

[news.microsoft.com]

Hi all,

I'm trying to create a transform query with VB6, ADO 2.8 and Access 2003.
I'm basically
doing the following:

'Create catalog connection
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = dll_ado.Connection

'Create command object
Set oCmd = New ADODB.Command
oCmd.CommandText = sSQL

'Add view to catalog
oCat.Views.Append sName, oCmd

But I get the error: Syntax error in Transform statement

If I add teh query using Access it works fine. It doesn't want to be added
when I do it through ADO.

Any ideas?

Thanks,
Ivan

 

[Brendan Reynolds]

Are you using any wildcards in your SQL statement? If so, are you using the
JET-specific wildcards '?' and/or '*'? If so, try replacing them with the
ANSI-standard wildcards '_' and/or '%'.

If that's not it, try posting the SQL.

 

[news.microsoft.com]

Hi,

Here's one of my transform statements:

CREATE VIEW search_records_crosstab AS TRANSFORM Max(search_records.value)
AS MaxOfvalue SELECT search_records.customer_id FROM search_records GROUP BY
search_records.customer_id PIVOT search_records.order_id

Ivan

 

[Jamie Collins]

Here's one of my transform statements:

CREATE VIEW search_records_crosstab AS TRANSFORM Max(search_records.value)
AS MaxOfvalue SELECT search_records.customer_id FROM search_records GROUP BY
search_records.customer_id PIVOT search_records.order_id

Try using CREATE PROCEDURE instead.

Jamie.

--

 

[Brendan Reynolds]

'Value' is a JET SQL reserved word
(http://support.microsoft.com/?kbid=248738). Try putting square brackets
around it ...

CREATE VIEW search_records_crosstab AS TRANSFORM Max(search_records.[value])
AS MaxOfvalue SELECT search_records.customer_id FROM search_records GROUP
BY
search_records.customer_id PIVOT search_records.order_id

 

[Brendan Reynolds]

Some further tests indicate that the field name isn't the only problem here.
Despite several attempts, I haven't been able to succeed in creating this
query via ADO. Would you settle for a solution that uses DAO instead?

Public Sub TestCreateCrosstab2()

Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "TRANSFORM Max(search_records.OrderValue) AS " & _
"MaxOfValue SELECT search_records.customer_id " & _
"FROM search_records GROUP BY search_records." & _
"customer_id PIVOT search_records.order_id"

Set qdf = New DAO.QueryDef
qdf.Name = "search_records_crosstab"
qdf.SQL = strSQL

'CurrentDb is Access-specific, use
'DbEngine.OpenDatabase in VB6.
CurrentDb.QueryDefs.Append qdf

End Sub

--
Brendan Reynolds
Access MVP

'Value' is a JET SQL reserved word
(http://support.microsoft.com/?kbid=248738). Try putting square brackets
around it ...

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.[value])
AS MaxOfvalue SELECT search_records.customer_id FROM search_records GROUP
BY
search_records.customer_id PIVOT search_records.order_id

--
Brendan Reynolds
Access MVP

Hi,

Here's one of my transform statements:

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.value) AS MaxOfvalue SELECT search_records.customer_id
FROM search_records GROUP BY search_records.customer_id PIVOT
search_records.order_id

Ivan

 

[Ivan Debono]

What do I need to include in my setup for DAO?

Some further tests indicate that the field name isn't the only problem
here. Despite several attempts, I haven't been able to succeed in creating
this query via ADO. Would you settle for a solution that uses DAO instead?

Public Sub TestCreateCrosstab2()

Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "TRANSFORM Max(search_records.OrderValue) AS " & _
"MaxOfValue SELECT search_records.customer_id " & _
"FROM search_records GROUP BY search_records." & _
"customer_id PIVOT search_records.order_id"

Set qdf = New DAO.QueryDef
qdf.Name = "search_records_crosstab"
qdf.SQL = strSQL

'CurrentDb is Access-specific, use
'DbEngine.OpenDatabase in VB6.
CurrentDb.QueryDefs.Append qdf

End Sub

--
Brendan Reynolds
Access MVP

'Value' is a JET SQL reserved word
(http://support.microsoft.com/?kbid=248738). Try putting square brackets
around it ...

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.[value])
AS MaxOfvalue SELECT search_records.customer_id FROM search_records GROUP
BY
search_records.customer_id PIVOT search_records.order_id

--
Brendan Reynolds
Access MVP

Hi,

Here's one of my transform statements:

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.value) AS MaxOfvalue SELECT
search_records.customer_id FROM search_records GROUP BY
search_records.customer_id PIVOT search_records.order_id

Ivan

Newsbeitrag Are you using any wildcards in your SQL statement? If so, are you using
the JET-specific wildcards '?' and/or '*'? If so, try replacing them
with the ANSI-standard wildcards '_' and/or '%'.

If that's not it, try posting the SQL.

--
Brendan Reynolds
Access MVP

message Hi all,

I'm trying to create a transform query with VB6, ADO 2.8 and Access
2003. I'm basically
doing the following:

'Create catalog connection
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = dll_ado.Connection

'Create command object
Set oCmd = New ADODB.Command
oCmd.CommandText = sSQL

'Add view to catalog
oCat.Views.Append sName, oCmd

But I get the error: Syntax error in Transform statement

If I add teh query using Access it works fine. It doesn't want to be
added
when I do it through ADO.

Any ideas?

Thanks,
Ivan

 

[Jamie Collins]

Despite several attempts, I haven't been able to succeed in creating this
query via ADO.

Did you try CREATE PROCEDURE as I suggested...?

Would you settle for a solution that uses DAO instead?

....because your DAO version still causes the 'query' to appear in the
PROCEDURES Rowset in the Information Schema, rather than the VIEWS
rowset e.g. run your DAO code then try:

Set rs = CurrentProject.Connection.OpenSchema(adSchemaViews)
rs.filter = "TABLE_NAME = 'search_records_crosstab'"
? rs.eof
True

Oops! No VIEW by that name.

Now try the procedures:

Set rs = CurrentProject.Connection.OpenSchema(adSchemaProcedures)
rs.filter = "PROCEDURE_NAME = 'search_records_crosstab'"
? rs!PROCEDURE_DEFINITION

There it is!

Conclusion: CREATE VIEW is the wrong syntax.

Jamie.

--

 

[Brendan Reynolds]

You'll need a reference to the Microsoft DAO 3.6 Object Library. I'm not
sure that you'll need to distribute it, though. I think its been bundled in
the OS since Windows 2000. But I have built only a few VB6 installations,
and none of them recent, so I'm probably not the best person to advise you
on that. If no one else joins in here, you might want to ask in a VB6 forum.

--
Brendan Reynolds
Access MVP

What do I need to include in my setup for DAO?

Some further tests indicate that the field name isn't the only problem
here. Despite several attempts, I haven't been able to succeed in
creating this query via ADO. Would you settle for a solution that uses
DAO instead?

Public Sub TestCreateCrosstab2()

Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "TRANSFORM Max(search_records.OrderValue) AS " & _
"MaxOfValue SELECT search_records.customer_id " & _
"FROM search_records GROUP BY search_records." & _
"customer_id PIVOT search_records.order_id"

Set qdf = New DAO.QueryDef
qdf.Name = "search_records_crosstab"
qdf.SQL = strSQL

'CurrentDb is Access-specific, use
'DbEngine.OpenDatabase in VB6.
CurrentDb.QueryDefs.Append qdf

End Sub

--
Brendan Reynolds
Access MVP

'Value' is a JET SQL reserved word
(http://support.microsoft.com/?kbid=248738). Try putting square brackets
around it ...

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.[value])
AS MaxOfvalue SELECT search_records.customer_id FROM search_records
GROUP BY
search_records.customer_id PIVOT search_records.order_id

--
Brendan Reynolds
Access MVP

Hi,

Here's one of my transform statements:

CREATE VIEW search_records_crosstab AS TRANSFORM
Max(search_records.value) AS MaxOfvalue SELECT
search_records.customer_id FROM search_records GROUP BY
search_records.customer_id PIVOT search_records.order_id

Ivan

Newsbeitrag Are you using any wildcards in your SQL statement? If so, are you
using the JET-specific wildcards '?' and/or '*'? If so, try replacing
them with the ANSI-standard wildcards '_' and/or '%'.

If that's not it, try posting the SQL.

--
Brendan Reynolds
Access MVP

message Hi all,

I'm trying to create a transform query with VB6, ADO 2.8 and Access
2003. I'm basically
doing the following:

'Create catalog connection
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = dll_ado.Connection

'Create command object
Set oCmd = New ADODB.Command
oCmd.CommandText = sSQL

'Add view to catalog
oCat.Views.Append sName, oCmd

But I get the error: Syntax error in Transform statement

If I add teh query using Access it works fine. It doesn't want to be
added
when I do it through ADO.

Any ideas?

Thanks,
Ivan

 

[Ivan Debono]

Thanks! It works like a charm!!

Ivan

Did you try CREATE PROCEDURE as I suggested...?


...because your DAO version still causes the 'query' to appear in the
PROCEDURES Rowset in the Information Schema, rather than the VIEWS
rowset e.g. run your DAO code then try:

Set rs = CurrentProject.Connection.OpenSchema(adSchemaViews)
rs.filter = "TABLE_NAME = 'search_records_crosstab'"
? rs.eof
True

Oops! No VIEW by that name.

Now try the procedures:

Set rs = CurrentProject.Connection.OpenSchema(adSchemaProcedures)
rs.filter = "PROCEDURE_NAME = 'search_records_crosstab'"
? rs!PROCEDURE_DEFINITION

There it is!

Conclusion: CREATE VIEW is the wrong syntax.

Jamie.