How to extract table names from a query (1 Viewer)

[mstres]

I have DB with a lot of queries and I need to find out witch one using special table with out open all of them in design view.
Or how to get list of tables using by this queries.
Thank you
Mike

 

[ByteMyzer]

The following VBA code example shows how to loop through the QueryDefs in the database and check the SQL string of each one to see if it contains the name of the table:

[COLOR=darkgreen]' Sample table name: MyTable[/COLOR]
[COLOR=navy]Dim[/COLOR] qd [COLOR=navy]As[/COLOR] QueryDef
[COLOR=navy]For Each[/COLOR] qd [COLOR=navy]In[/COLOR] DBEngine(0)(0).QueryDefs
    [COLOR=navy]If[/COLOR] InStr(1, qd.SQL, "MyTable", vbTextCompare) > 0 [COLOR=navy]Then[/COLOR]
[COLOR=navy]       Debug[/COLOR].[COLOR=navy]Print[/COLOR] qd.Name
    [COLOR=navy]End If[/COLOR]
[COLOR=navy]Next[/COLOR] qd

 

[mstres]

Thank you very much. It is realy help.
This is exactly what I am looking for.
Mike

 

[raskew]

Hi -

To carry this a step further, the following sub, copied/pasted to a standard
module, will create/recreate tblQueries4 and populate it with the name of
each query in the database, along with the name of the source table(s)/
query(s) for each respective query.

Public Sub GetQueries4()
'*******************************************
'Name:      GetQueries4 (Function)
'Purpose:   Creates tblQueries4 and populates
'           with query record source info.
'Coded by:  raskew
'Inputs:    From debug (immediate) window
'           Call GetQueries4
'Outputs:   tblQueries4
'*******************************************

Dim db   As Database
Dim rs   As Recordset
Dim rs2  As Recordset
Dim tName As String, tsource As String
Dim n As Integer, i As Integer, fcount As Integer
Dim x As Integer, z As Integer
Dim strSQL As String

Set db = CurrentDb

On Error Resume Next
tName = "tblQueries4"
'Does table "tblQueries4" exist?  If true, delete it;
   db.Execute "DROP TABLE " & tName & ";"
'Create new tblTable
db.Execute "CREATE TABLE tblQueries4(ObjectID LONG, " _
 & " Type TEXT (55), Object TEXT (55), " _
 & " RecordSource TEXT (55));"
 
strSQL = "SELECT A.Name, B.Name1" _
    & " FROM MSysObjects AS A, MSysQueries AS B" _
    & " WHERE (((A.Id)=[B].[ObjectID]) AND" _
    & " ((B.Expression) Is Null) AND" _
    & " ((Left([Name],1))<>'~') AND ((Left([Name1],1))<>'['))" _
    & " ORDER BY A.Name;"

Set rs = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset("tblQueries4")

rs.MoveLast
n = rs.RecordCount
rs.MoveFirst

For i = 0 To n - 1
   tName = rs!Name
   rs2.AddNew
   With rs2
     !ObjectID = 2
     !Type = "Query"
     !Object = tName
     !RecordSource = rs!Name1
     .Update
   End With
   rs.MoveNext
Next i

rs.Close
rs2.Close
db.Close
Set db = Nothing
     
End Sub

HTH - Bob

 

[gemma-the-husky]

bob

thanks for that snippet - I think this will be a useful bit of stuff to have, so thanks for saving me a lot of effort.

 

[raskew]

Dave -

Glad you found it helpful. I wrote it eons ago and had to grope around to find it.

Incidentally, I've got a number of similar variations already written. If you've got other needs, please post them and perhaps I can help out.

Best wishes - Bob

 

[Uncle Gizmo]

That's a nice bit of kit....

Thank you Bob,

 

[gemma-the-husky]

i've had a look at it now, and added a pre-count and progress meter. The first time it took a few minutes (used it on an app with other 700 queries, and 2800 sub queries/tables used in those queries), but after the first time, only a few moments.

I am not familiar with the msys type tables - I wondered if there was any simple way of distinguishing between whether an object is a TABLE, or a QUERY, from the msysqueries table. I couldnt immediately see one.

 

[raskew]

Dave -
After playing with this for a while, I'm afraid I have to agree. Don't find a reliable way to designate table vs. query as the record source. It doesn't help that MSys is undocumented in the help file.

Would sure be interested if someone were able to come up with a method.

Best Wishes - Bob

 

[DCrake]

Here is a function that determines whether the obejct is a table or a query

Function IsTableQuery(DbName As String, TName As String) As Integer

   Dim db As DAO.Database, Found As Integer, test As String
   Const NAME_NOT_IN_COLLECTION = 3265

   ' Assume the table or query does not exist.
   Found = False

   ' Trap for any errors.
   On Error Resume Next

   ' If the database name is empty...
   If Trim$(DbName) = "" Then
      ' ...then set Db to the current Db.
      Set db = CurrentDb()
   Else
      ' Otherwise, set Db to the specified open database.
      Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)

      ' See if an error occurred.
      If Err Then
         MsgBox "Could not find database to open: " & DbName
         IsTableQuery = False
         Exit Function
      End If
   End If

   ' See if the name is in the Tables collection.
   test = db.TableDefs(TName).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   ' Reset the error variable.
   Err = 0

   ' See if the name is in the Queries collection.
   test = db.QueryDefs(TName$).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   db.Close

   IsTableQuery = Found

End Function

David