Firstly, make-table queries have a different icon that regular select
queries, so it shouldn't be that hard to reduce the overall list to check
through. That said, I put together a quick little function to locate which
Query creates a table. Try it out, hopefully it will help.
'---------------------------------------------------------------------------------------
' 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
--
Hope this helps,
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
