ParentTable property of fields in pass-through query? 2

[Ed2020]

Hi,

I am attempting to write some code to identify all pass-through queries in an Access database, and return a list of all tables & fields used in them.

The problem I have is that when iterating through the Fields collection the Field.ParentTable property only returns the name of the pass-through query, not the name of the underlying database table.

Is there anywhere else I can get this information from?

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[jsteph]

Ed,
A passthru is pure text. There are no table or field properties like those in a Jet query.
--Jim

 

[jsteph]

Ed,
...as to where else you can get this from, you'd need to do parsing of the text, which may not be feasible.

For instance, a pt query might contain a stored procedure that returns a recordset.

So bottom line, this might be a manual process.
--Jim

 

[jrbarnett]

You can get the fields by executing the recordset with a where clause that will return no records, then evaluating the recordset fields collection.

eg: note the condition that will always return am empty result set:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.OpenRecordSet ("SELECT * FROM myPassThroughQuery where 1 = 2")

You can then pass through the rs.Fields collection to obtain the fields:

Dim i as Integer
For i = 0 To qdf.Fields.Count - 1
  Debug.Print qdf.Fields(i).Name
Next i

Other properties of the qdf object include the data type and size of the field.

John

 

[Ed2020]

Thanks both,

John's suggestion worked for returning the field names. I had something similar but hadn't thought of adding the WHERE clause to return no data - that's much faster now.

The pass-through queries are running against an Oracle database, so I am hoping that I'll be able to retrieve the parent table name by querying the ALL_TAB_COLUMNS table.

Thanks again - stars for both of you.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

In case anybody wants to do anything similar in the future my (currently untested) solution looks like this:

Private conODS As ADODB.Connection

Public Sub PassThroughTest()
    Dim ThisDB As DAO.Database
    Dim QDef As DAO.QueryDef
    Dim MyField As DAO.Field
    Dim strFieldList As String
    
    Set ThisDB = CurrentDb()
    Set conODS = New ADODB.Connection
    conODS.Open "Driver={Oracle73 Ver 2.5};DSN=aaa;UID=bbb;PWD=ccc;DBQ=ddd;ASY=OFF;"
    
    For Each QDef In ThisDB.QueryDefs
        If QDef.Connect <> "" Then 'It's a pass-through
            For Each MyField In QDef.Fields
                strFieldList = strFieldList & vbCrLf & ParentTable(MyField.Name, QDef.SQL) & "." & MyField.Name
            Next MyField
        End If
    Next QDef

    Debug.Print strFieldList

End Sub

Public Function ParentTable(ByVal strFieldName As String, ByVal strQuerySQL As String) As String
    On Error GoTo TrapErr
    
    Dim rstDataDictionary As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
    Dim astrMultiples() As String
        
    strSQL = "SELECT TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='" & strFieldName & "';"
    
    Set rstDataDictionary = New ADODB.Recordset
    rstDataDictionary.CursorLocation = adUseClient 'This is required to use the .MoveLast method. Defaults to adUseServer
    rstDataDictionary.Open strSQL, conODS, adOpenStatic, adLockReadOnly

    'Ensure .RecordCount property returns correct value. Is this necessary on ADO recordsets??
    rstDataDictionary.MoveLast
    rstDataDictionary.MoveFirst

    
    If rstDataDictionary.RecordCount = 1 Then 'Only one table in ODS contains this field. Good news!
        ParentTable = rstDataDictionary!TABLE_NAME
    ElseIf rstDataDictionary.RecordCount > 1 Then 'Bugger. Multiple tables in ODS have this field. Now we need to find the relevant one.
        With rstDataDictionary
            Do While Not .EOF
                If InStr(1, strQuerySQL, !TABLE_NAME, vbTextCompare) > 0 Then
                    strList = strList & "," & !TABLE_NAME
                End If
                .MoveNext
            Loop
            
            strList = Right$(strList, Len(strList) - 1)
            astrMultiples = Split(strList, ",")
            
            If UBound(astrMultiples()) > 0 Then 'More than one table
                ParentTable = "MULTIPLES"
            Else
                ParentTable = astrMultiples(0)
            End If
        End With
    Else 'Table count = zero. This should never happen...
        ParentTable = "UNKNOWN"
    End If
    
ExitHere:
    Exit Function
    
TrapErr:
    ParentTable = "UNEXPECTED_EXCEPTION"
    Resume ExitHere

End Function

If anyone can spot any problems with it I'd appreciate a heads-up!

Ed Metcalfe.

Please do not feed the trolls.....

 

[jsteph]

Ed,
Your original suggestion may have worked--I was incorrect--the passthru querydef will contain a fields collection (it appears to do what jrbarnett suggested and creates a recordset behind-the-scenes), so you'll be ok there.

But I'd caution you to beware of pass-thru queries that may contain stored procedures or aliases--in the former the query sql won't contain any relevant info, in the latter the alias is returned from the .Fields collection.

However, if all the passthru's are straightforward SELECT's, and don't use asterisk or alias for the field-list, then you can likely get by on that.
--Jim

 

[Ed2020]

Thanks Jim,

I'm confident that none of our pass-through queries contain stored procedures.

SELECT * seems to get analysed correctly. I hadn't considered aliases. I think they will simply fail to identify the parent table name. I'll need to look at these manually, but at least they'll be the exception.

Thanks for your input.

ED Metcalfe.

Please do not feed the trolls.....