ADO equivalent of dbQCrosstab?

  • Thread starter Thread starter pietlinden
  • Start date Start date
P

pietlinden

I'm wondering if I'm just missing something...

I am processing a collection of views/queries in a listbox (this part
is fine).

The problem is that I want to process Crosstabs in a different way
than I want to process normal Selects. In DAO, this is pretty
simple... just use DbEngine(0)(0).Querydefs("QueryName").Type to
determine what kind of query it is.

How do you do the same thing in ADO?
 
I'm wondering if I'm just missing something...

I am processing a collection of views/queries in a listbox (this part
is fine).

The problem is that I want to process Crosstabs in a different way
than I want to process normal Selects.  In DAO, this is pretty
simple... just use DbEngine(0)(0).Querydefs("QueryName").Type to
determine what kind of query it is.

How do you do the same thing in ADO?
Click to expand...

I came up with this ugly workaround... it sort of works (I think...)
But I figured there would be an easier way... Maybe not...

But it's really awkward in ADO... in DAO, I can just loop through a
series of querydefs and just ask what type it is...

Select case qdf.Type
Case dbQSelect
'do one thing
Case dbQCrosstab
'do something else
Case Else
'do nothing
End Select

Public Function IsCrosstab(ByVal strViewName As String) As Boolean
On Error GoTo IsCrosstabError

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command


' Open the connection to the local database
Set cnn = CurrentProject.Connection
' Open the catalog
Set cat.ActiveConnection = CurrentProject.Connection

' point at the chosen object (if it exists)
Set cmd = cat.Procedures(strViewName).Command
IsCrosstab = Left$(cmd.CommandText, 9) = "TRANSFORM"
'retrieve field information

Set cmd = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

Exit Function

IsCrosstabError:
' Debug.Print "Error Number: " & Err.Number & vbCrLf & "Text: &
err.Description"
IsCrosstab = False
Set cmd = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Function
 
I came up with this ugly workaround... it sort of works (I think...)
But I figured there would be an easier way... Maybe not...

But it's really awkward in ADO... in DAO, I can just loop through a
series of querydefs and just ask what type it is...

Select case qdf.Type
Case dbQSelect
'do one thing
Case dbQCrosstab
'do something else
Case Else
'do nothing
End Select

Public Function IsCrosstab(ByVal strViewName As String) As Boolean
On Error GoTo IsCrosstabError

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command


' Open the connection to the local database
Set cnn = CurrentProject.Connection
' Open the catalog
Set cat.ActiveConnection = CurrentProject.Connection

' point at the chosen object (if it exists)
Set cmd = cat.Procedures(strViewName).Command
IsCrosstab = Left$(cmd.CommandText, 9) = "TRANSFORM"
'retrieve field information

Set cmd = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

Exit Function

IsCrosstabError:
' Debug.Print "Error Number: " & Err.Number & vbCrLf & "Text: &
err.Description"
IsCrosstab = False
Set cmd = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Function
Click to expand...

You'd probably need

IsCrosstab = InStr(cmd.CommandText, "TRANSFORM") > 0

cause you may have parameters, which is defined at the "top of the
SQL".

You could probably also use the OpenSchema method, say (air code)

Public Function IsCrosstab(ByVal strViewName As String) As Boolean

Dim rs As ADODB.Recordset
Dim f As Boolean

f = False
Set rs = CurrentProject.Connection.OpenSchema( _
adSchemaProcedures, Array(Empty, Empty, strViewName))
If ((Not rs.Bof) And (Not rs.EOF)) Then
f = InStr(rs.Fields("PROCEDURE_DEFINITION").Value, _
"TRANSFORM") > 0
End If
IsCrosstab = f
rs.Close
Set rs = Nothing

End Function

But, why not use DAO?
 
You'd probably need

IsCrosstab = InStr(cmd.CommandText, "TRANSFORM") > 0

cause you may have parameters, which is defined at the "top of the
SQL".

You could probably also use the OpenSchema method, say (air code)

Public Function IsCrosstab(ByVal strViewName As String) As Boolean

    Dim rs           As ADODB.Recordset
    Dim f            As Boolean

    f = False
    Set rs = CurrentProject.Connection.OpenSchema( _
           adSchemaProcedures, Array(Empty, Empty, strViewName))
    If ((Not rs.Bof) And (Not rs.EOF)) Then
        f = InStr(rs.Fields("PROCEDURE_DEFINITION").Value, _
            "TRANSFORM") > 0
    End If
    IsCrosstab = f
    rs.Close
    Set rs = Nothing

End Function

But, why not use DAO?
Click to expand...

I was looking for an alternative because I'm using ADO to convert
everything to delimited strings, which I can easily convert to tables
in Word. Maybe I should go the easy way, and write something to
convert the DAO recordset to a delimited string? Might just be going
about this ass backwards... Wouldn't be the first time, certainly, and
I doubt it will be the last.
 
Back
Top