DEVelopers HUT

'---------------------------------------------------------------------------------------
' Procedure : findmaketbl
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Locate which Make-Table Query is creating a table
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName    Name of the Table that you believe is created by a Make-Table Query.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
'
**************************************************************************************
' 1         2009-Jun-12                 Initial Release
'---------------------------------------------------------------------------------------
Function findmaketbl(sTableName As String)
On Error GoTo Error_Handler
 
    Dim db      As DAO.Database
    Dim qdf     As DAO.QueryDef
    Dim sSQL    As String
 
    Set db = CurrentDb
 
    For Each qdf In db.QueryDefs
        'qdf.Name    'The current query's name
        'qdf.SQL     'The current query's SQL statement
        sSQL = qdf.SQL
        If InStr(sSQL, " INTO ") And InStr(sSQL, sTableName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            MsgBox "Query:'" & qdf.Name & "' is a Make-Table Query for Table '" & _
                    sTableName & "'.", vbInformation
        End If
    Next
 
    Set qdf = Nothing
    Set db = Nothing
 
If Err.Number = 0 Then Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: findmaketbl" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function