Access 2003 Transform Queries??

N

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
 
B

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.
 
N

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
 
B

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
 
B

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


Brendan Reynolds said:
'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

news.microsoft.com said:
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
Click to expand...
Click to expand...
 
I

Ivan Debono

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

Brendan Reynolds said:
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


Brendan Reynolds said:
'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

news.microsoft.com said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

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

Ivan Debono said:
What do I need to include in my setup for DAO?

Brendan Reynolds said:
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


Brendan Reynolds said:
'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
Click to expand...
Click to expand...
Click to expand...
 
I

Ivan Debono

Thanks! It works like a charm!!

Ivan

Jamie Collins said:
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.
Click to expand...
 
Top