VBA To Return Field and Table List? 2

[Ed2020]

Hi,

I need to create some VBA to analyse an SQL statement and provide a list of tables and fields referenced in the SQL.

I've made a start, but the further I get into it the more complex it's getting!

Does anyone already have anything that does this?

Unfortunately I can't interrogate the Fields collection of the QueryDef - it has to be done by working with the SQL string. :-(

Any help much appreciated!

Ed Metcalfe.

Please do not feed the trolls.....

 

[jrbarnett]

If its all in one table - can you select the fields and tables from information_schema (for SQL tables) ?
Unfortunately doing this for queries with joins in makes it far more complex.

You can also do this with ADO in a similar way to using the fields collection of a QueryDef object, but can't quite remember off the top of my head how to do this. Can post back later if interested.

John

 

[PHV]

I can't interrogate the Fields collection of the QueryDef - it has to be done by working with the SQL string
Can't you play with a temporary QueryDef defined with the SQL string ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ed2020]

John/PHV,

Both excellent suggestions. I will most likely go with PHV's as I am more familiar with DAO and this is something I need to get completed as soons as possible. I'm kicking myself for not thinking of this solution myself!

John - Thanks for the heads-up on the ADO option. I'll do some further research on this at a later date.

Purple stars all round!

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Found a slight problem.

Some of the SQLs are retrieving data from an Oracle data source.

If I define a pass-through query def and retrieve the Field.SourceTable property the property value is the name of the pass-through query, not the Oracle table. :-(

Does anyone have any suggestions?

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[jrbarnett]

Here's how to get a table and field list via ADO and ADOX.

Function ShowTableListViaADO()
    
    ' Requires ADO and ADOX for DDL and security references

    Const connect = "Provider=SQLOLEDB;Server=(local);User ID=xxx;Password=yyy;Initial Catalog=Northwind;Network Library=dbmssocn;App=My App"
    
    Dim i As Integer
        
    Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim col As ADOX.Column
    
    Dim tbl As ADOX.Table
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = connect
        .CursorLocation = adUseClient
        .Open
    End With

    Set cat = New ADOX.Catalog
    With cat
        .ActiveConnection = cn
        For i = 0 To .Tables.Count - 1
            Debug.Print "Table: " & .Tables(i).Name
            
            Set tbl = cat.Tables(i)
            For Each col In tbl.Columns
                Debug.Print "** " & col.Name
            Next col
        Next i
    End With

End Function

If I figure out how to get the underlying table for a specified query string I'll post here.

John

 

[Ed2020]

Thank you John. Your help is much appreciated.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Well, I've solved the problem of retrieving tables names by pulling the table list from the FROM clause of the SQL statement.

I have created the code below to retrieve the field names from a recordset, however I've just realised this isn't going to return the names of fields that appear in the WHERE clause but not in the SELECT statement.

If anyone has any suggestions to resolve this I'd very much appreciate it! Pulling my hair out here - as soon as I resolve one problem I find another! :-(

Public Sub ParseFields()
    Dim rstSQL As New ADODB.Recordset
    Dim MyField As ADODB.Field
    Dim strFieldList As String
    
    rstSQL.Open m_strSQL, conODS

    For Each MyField In rstSQL.Fields
        strFieldList = strFieldList & "," & MyField.Name
    Next MyField
    
    strFieldList = Right$(strFieldList, Len(strFieldList) - 1)
    m_strFields() = Split(strFieldList, ",")
    
    m_intFieldCount = UBound(m_strFields) + 1
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[ZmrAbdulla]

Recently Remou had posted a code somewhere here..
I can't find the thread now but the code is here

Function ListFieldDescriptions()
'List field descriptions
    Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
    Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
    Dim connString As String

    Set cn = CurrentProject.Connection

    Set rs = cn.OpenSchema(adSchemaTables, _
                           Array(Empty, Empty, Empty, "table"))

    While Not rs.EOF
        Debug.Print rs!table_name; " desc= "; rs!Description
        Set rs2 = cn.OpenSchema(adSchemaColumns, _
                                Array(Empty, Empty, "" & rs!table_name & ""))
        While Not rs2.EOF
            Debug.Print " " & rs2!Column_Name
            Debug.Print " " & rs2!Data_Type
            Debug.Print " " & rs2!Description
            Debug.Print " " & rs2!Is_Nullable
            rs2.MoveNext
        Wend
        rs.MoveNext
    Wend
    rs.Close
    Set cn = Nothing

End Function

adSchemaTables for tables
adSchemaViews for Select queries
adSchemaProcedures for Action queries

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[Ed2020]

Thanks Zameer, but I think that code will present the same issue that I already have - it will only provide details of fields contained within the SELECT clause. I also need to identify fields referenced in the the WHERE clause (some of the SQLs are Oracle SQL, so joins will be defined in the WHERE clause).

Ed Metcalfe.

Please do not feed the trolls.....

 

[jrbarnett]

How about as a workaround, create a view for the SQL, then use the Oracle data dictionary to pull it off by querying the views?

http://w2.syronex.com/jmr/edu/db/oracle-data-dictionary/

John

 

[Ed2020]

Solved it.

If anyone's interested my method was as follows:

Public Sub ParseFields()
    Dim pntr As Integer
    Dim MyCat As ADOX.Catalog
    Dim TDef As ADOX.Table
    Dim MyField As ADOX.Column
    Dim strFieldList As String
    
    Set MyCat = New ADOX.Catalog
    MyCat.ActiveConnection = conODS
    
    For pntr = 0 To UBound(m_strTables())
        Set TDef = MyCat.Tables(m_strTables(pntr))
        
        For Each MyField In TDef.Columns
            If InStr(1, m_strSQL, MyField.Name) > 0 Then
                strFieldList = strFieldList & "," & MyField.Name
            End If
        Next MyField
    Next pntr
    
    strFieldList = Right$(strFieldList, Len(strFieldList) - 1)
    m_strFields = Split(strFieldList, ",")
    m_intFieldCount = UBound(m_strFields())
End Sub

Ed Metcalfe

Please do not feed the trolls.....