Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Loop through all queries in db and if qry i want print the sql

    i gave it a try and didnt get far.., i would like to print the sql of a qry, i want to do a vba sub , below is an idea, i know its wrong

    sub printQry

    dim sql as string
    dim qry as ???

    for each accessObj in current.db

    if accessObj.Name = "QryIamLoookingFor"
    debug.print accessObj.sql

    next

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Here...this wil get ya going:

    Code:
    Dim i As Integer
    For i = 0 To CurrentDb.QueryDefs.Count - 1
       MsgBox CurrentDb.QueryDefs(i).Name
       MsgBox CurrentDb.QueryDefs(i).SQL
    Next i
    How you organize this is up to you. What you could do I guess is:

    Put the Names in a ListBox then when You select a Query name from the list box have the SQL display in a TextBox. Ya...that would be kinda neat.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by CyberLynx
    Here...this wil get ya going:

    Code:
    Dim i As Integer
    For i = 0 To CurrentDb.QueryDefs.Count - 1
       MsgBox CurrentDb.QueryDefs(i).Name
       MsgBox CurrentDb.QueryDefs(i).SQL
    Next i
    How you organize this is up to you. What you could do I guess is:

    Put the Names in a ListBox then when You select a Query name from the list box have the SQL display in a TextBox. Ya...that would be kinda neat.

    .
    Hi again CyberLynx,

    Could you be so kind as to explain this bit of code you just displayed. I see it often but don't understand it at all. You guys amaze me with how you just create this stuff with seemingly such eaze. So if it's not to much trouble could you give me a quick breakdown of what it is, does and such.

    thanks and have a nice one,
    Bud

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Bud, I applaud you!
    It's rare to find someone who wants to understand, more that simply get a solution
    The above code uses a For Loop to cycle through the QueryDef(inition) collection and for each one display it's name followed by the SQL statement contained in the definition.

    You may note the use of
    Code:
    QueryDefs(i)
    Where i is an integer value. We refer to i here as the ordinal of the collection.

    I feel I should have some nice summary line her, but I don't... If I've not explained this well enough then let me know
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Bud,

    georgev
    has pretty much explained it all. It's just a simple matter of cycling through the QueryDefs collection (for Queries) as you can also do with TableDefs collection (for Tables), AllMacros collection(for Macros), Modules or AllModules collection (for Modules), and so on and so on.

    Look in MS-Access Help. Search for Collection or Collections.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Oct 2003
    Posts
    233
    Thanks, that was very helpful, now i want to try and find a column in a table:
    will this do it?

    For i = 0 To CurrentDb.QueryDefs.count - 1

    For j = 0 To CurrentDb.TableDefs(i).Fields.count - 1
    If InStr(CurrentDb.TableDefs(i).Fields(j).Name, "CompanyRecType2") Then
    Debug.Print CurrentDb.TableDefs(i).Name
    Debug.Print CurrentDb.TableDefs(i).Fields(j).Name
    End If
    Next j
    Next i

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    The QueryDefs and TableDefs are two different Collections.
    What exactly are you trying to do here anyway? What is the ulitmate goal?

    Here is a Function to List Table Names in a Database either local or remote . Ths Function name is ListDBTables and within it it also make use of another Function named IsArrayActive. Both are listed below and if you want to use them then place them into a Database code module:

    The ListDBTables makes use of an String Array Variable we'll call conveniently enough, TablesArray:

    Code:
    Public Function ListDBTables(ByVal DBName As String, ByRef TablesArray() As String, Optional ByVal ShowType As Integer) As Long
       'DAO 3.x required.
    
       'Parameters.....
       'DBName = The Path and File Name of the remote Database to get the table list
       '         from. If nothing is assed here then the local database is assumed.
       'TablesArray() = This parameter is Declared from where this Function was
       '                Called. The Array is passed empty and will be returned
       '                with the Table Names from the supplied Database.
       'ShowType = This parameter is optional. Within the TableDefs collection 
       '           there are either System Tables, Non-System Tables (tables
       '           you made), and Hidden Tables. If 0 or nothing is passed we are 
       '           telling the Function to fill the Array with Table Names of
       '           tables that are Non-System and Visible. If 1 is passed then
       '           we are telling the Function to fill the Array with Table names
       '           of tables that are Hidden Tables only. If 2 is passed then
       '           we are telling the Function to fill the Array with Table Names
       '           of tables that are System Tables only. If 3 is passed then we are
       '           telling the Function to fill the Array with Table Names of
       '           all tables (System, Hidden, or otherwise). Default is 0
        '
       'Over all...this Function will return the number of Tables it has placed into
       'the TablesArray Array.
    
       'Declare Variables
       Dim db As Database
       Dim i As Integer, x As Integer
       
       'If no Database path and file name was supplied then
       'we assume the database to work with will be the local
       'Database (the Database this Function is running from).
       If DBName = "" Then
          Set db = CurrentDb    'Set the db variable to this Database.
       Else
          Set db = OpenDatabase(DBName)  'Set the db variable to a specified Database. 
       End If
    
       'Cycle through the supplied Database TabeDefs collection....   
       For i = 0 To db.TableDefs.Count - 1
          'Check the Table Name and passed ShowType and fill the TableName array accordingly.
          Select Case ShowType
             Case 0   ' Indicate Only Non-System and Visible Tables.
                'Access System tables Start with either MSys or USys. If the Table
                'Name doesn't start with either then place this table name into the
                'Array.
                If Left$(db.TableDefs(i).Name, 4) <> "MSys" And Left$(db.TableDefs(i).Name, 4) <> _
                   "USys" And Left$(db.TableDefs(i).Name, 1) <> "~" Then
                   'Go to the routine within this function at the bottom and set 
                   'the Table name into the TableNames Array.
                   GoSub SetTablesArray
                End If
    
             Case 1   ' Indicate Only Hidden Tables.
                'If the Table Name starts with a Tilde (~) then it's a Hidden Table.
                'Since this is what we want then....
                If Left$(db.TableDefs(i).Name, 1) = "~" Then
                   'Go to the routine within this function at the bottom and set 
                   'the Table name into the TableNames Array. 
                   GoSub SetTablesArray
                End If
    
             Case 2   ' Indicate Only System Tables.
                'If the Table Name does start with either MSys or USys then
                'thisis what we want so place this table name into the Array.
                If Left$(db.TableDefs(i).Name, 4) = "MSys" Or Left$(db.TableDefs(i).Name, 4) = _
                   "USys" Then
                   'Go to the routine within this function at the bottom and set 
                   'the Table name into the TableNames Array. 
                   GoSub SetTablesArray
                End If
    
             Case 3   ' Indicate ALL Tables (System, Hidden, or otherwise).
                'Go to the routine within this function at the bottom and set 
                'the Table name into the TableNames Array with every Table
                'name in the Database. 
                GoSub SetTablesArray
          End Select
       Next i
    
       'Close the Database connection.
       db.Close
    
       'Free up memory.
       Set db = Nothing
    
       'Is there anything in the TableNames Array? If so then
       let this Function pass back the number of names.
       If IsArrayActive(TablesArray()) = True Then ListDBTables = UBound(TablesArray())
       'Get outta here.
       Exit Function
       
    SetTablesArray:
       'ReDimension the Array with Preservation to our first element which is
       'determined by the Integer variable x. Using Preservation prevents
       'previously filled array elements from getting wiped out.
       ReDim Preserve TablesArray(x)
    
       'Place the Table Name into the TablesArray
       TablesArray(x) = db.TableDefs(i).Name
    
       'Increment x by 1.
       x = x + 1
     
       'Return to where the Gosub was called and continue on processing.
       Return
    End Function
    
    
    Public Function IsArrayActive(TableNames() As String) As Boolean
       'This function simply checks to see if there is any elements within the
       'TablesArray array. Rather than checking through error in the main 
       'function, it's done here.
    
       'If the array contains elements then True is returned 
       'otherwise False is returned.
       
       'Trap Errors...
       On Error Resume Next
       'Clear any previous errors before trap takes place.
       Err.Clear
       'Declare the x Integer variable.
       Dim x As Integer
    
       'Place the number of elemnts into x. If there are none an error will occure.
       x = UBound(TableNames)
       'If there was an error then....
       If Err > 0 Then
          'Clear the Error
          Err.Clear
          'Let this Function return False
          IsArrayActive = False
       Else  'If there was no Error then
          'Let this Function return True
          IsArrayActive = True
       End If
    End Function
    Looks intimidating but it's really all comments.
    .
    Last edited by CyberLynx; 11-21-07 at 23:53.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    How to use the above Function in above post:

    Place the following code into the OnClick event of a Command Button. It should look like something like this:

    Code:
    Private Sub Command1_Click()
       'Declare Variables
       Dim Cnt As Integer
       Dim Strg As String
       Dim DBtoView As String
       Dim ViewWhat As Integer
       Dim TablesArray() As String
       
       'Fill the DBtoView variable wth the Path and File name of the Database
       'you want to get the Table names from. If an empty string ("") is
       'supplied then the table names from the Database that this routine is
       'running in will be processed.
       DBtoView = "C:\WORKDBs\SomeDatabase.mdb"
       
       'Indicate which Table names you want to get from the supplied Database.
       'See the Select Case statement comments below.
       ViewWhat = 3 ' optional
       
       'Call the ListDBTables Function and Fill The TablesArray array.
       'Note that the TablesArray() array was Delared in this event. It
       'it being passed through to the ListDBTables Function. When the
       'ListDBTables Function is finished, the contents of this Array remain
       'intact.
       Cnt = ListDBTables(DBtoView, TablesArray(), ViewWhat)
                          
       'Depending on the ViewWhat you chose  a MsgBox will display the 
       'desired Table Names.
       Select Case ViewWhat
          Case 0    ' Indicate Only Non-System and Visible Tables.
             Strg = "The User Tables located within the " & DBtoView & _
             " are:" & vbCrLf
          Case 1    ' Indicate Only Hidden Tables.
             Strg = "The Hidden Tables located within the " &  DBtoView & _
             " are:" & vbCrLf
          Case 2    ' Indicate Only System Tables.
             Strg = "The MS-Access System Tables located within the " &  DBtoView & _
             " are:" & vbCrLf
          Case 3    ' Indicate ALL Tables (System, Hidden, or otherwise).
             Strg = "ALL the Tables located within the " & DBtoView & _
             " are:" & vbCrLf
       End Select
       
       'Extract the content of each element within the TabalesArray array and
       'place it into the Strg string variable. A Carriage Return/LineFeed
       'combination is added to the end of each Table name for listing purposes
       'only.
       If Cnt > 0 Then
          Dim i As Integer
          For i = LBound(TablesArray()) To UBound(TablesArray())
             Strg = Strg & TablesArray(i) & vbCrLf
          Next i
       End If
       
       'Display the Table Names via message box.
       MsgBox"There were " & Cnt & " Tables found." & vbCr & vbCr & Strg,  _
                 vbInformation, "ListDBTables Function..."
    
       'Free memory - wipe out the TableArray array.
       Erase TablesArray()
    End Sub
    Yes..I know you want to get Table Field names but first things first. The next post will pull out the Field Names from a Table. You can only fit so much in one of these posts.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    Now to list Field Names in a Table we can use this Function and if you have read the comments in the previous posts, they shouldn't be needed here. The only difference with this function is the Array name. Now we are using the FieldsArray array :

    Code:
    Public Function ListDBTBLFields(ByVal DBName As String, ByVal TableName As String, _
                    ByRef FieldsArray() As String, Optional ByVal ShowType As Integer) _
                    As Long
       Dim db As Database
       Dim i As Integer
       Dim x As Integer
       
       If DBName = "" Then
          Set db = CurrentDb
       Else
          Set db = OpenDatabase(DBName)
       End If
    
       For i = 0 To db.TableDefs(TableName).Fields.Count - 1
          x = x + 1
          ReDim Preserve FieldsArray(x)
          FieldsArray(x) = db.TableDefs(TableName).Fields(i).Name
       Next i
       db.Close
       Set db = Nothing
    
       If IsArrayActive(FieldsArray()) = True Then ListDBTBLFields = UBound(FieldsArray())
    End Function
    Doesn't look have bad with all the comments out of it...does it.

    How to use the Above Function:

    Again in the OnClick event of a Command Button:

    Code:
    Private Sub ListDBTblFieldsButton_Click()
       Dim Cnt As Integer
       Dim Strg As String
       Dim DBtoView As String
       Dim TblToView As String
       Dim FieldsArray() As String
       
       DBtoView = "C:\WORKDBs\SomeDatabase.mdb"
       TblToView = "theTableName"
       
       Cnt = ListDBTBLFields(DBtoView, TblToView, FieldsArray())
                          
       
       If Cnt > 0 Then
          Dim i As Integer
          For i = LBound(FieldsArray()) To UBound(FieldsArray())
             Strg = Strg & FieldsArray(i) & vbCrLf
          Next i
       End If
       
       MsgBox"There were " & Cnt & " Fields found in " & vbCrLf & _
                   "the Table named:  '" & TblToView & "'" & vbCr & vbCr & Strg, _
                   vbInformation, "ListDBTBLFields Function...")
       Erase FieldsArray()
    End Sub
    I aplogize if there are any errors. If so please let me know.


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •