(E-Mail Removed) wrote:
> On Jan 23, 6:54*pm, "pietlin...@hotmail.com" <pietlin...@hotmail.com>
> wrote:
>> 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 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
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?
--
Roy-Vidar
Similar Threads
