[AccessD] Changing the table in a query so I can loop through the query

If you are just appending fields  modified slightly from Access97 help



'Append and Delete Methods Example
'This example uses either the Append method or the Delete method to 
modify the Fields collection of a TableDef. The AppendDeleteField 
procedure is required for this procedure to run.

Sub AppendX()

    Dim dbs As Database
    Dim tdf As TableDef
    Dim fldLoop As Field
    Dim strTdef As String
    Set dbs = CurrentDb
      'Import tables from specified Access database or currentDB
    For Each tdf In dbs.TableDefs 'loop through tables for names
    strTdef = tdf.Name
    Debug.Print strTdef
    If Left(strTdef, 4) <> "MSys" Then ' skip system tables

    ' Add three new fields. 'you may want to add other options like no 
zero length etc
    AppendDeleteField tdf, "APPEND", "E-mail", dbText, 50
    AppendDeleteField tdf, "APPEND", "Http", dbText, 80
    AppendDeleteField tdf, "APPEND", "Quota", dbInteger, 5

    Debug.Print "Fields after Append to Table " & tdf.Name

    Debug.Print , "Type", "Size", "Name"

    ' Enumerate the Fields collection to show the new fields.
    For Each fldLoop In tdf.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop

    ' Delete the newly added fields.
   ' AppendDeleteField tdf, "DELETE", "E-mail"
   ' AppendDeleteField tdf, "DELETE", "Http"
   ' AppendDeleteField tdf, "DELETE", "Quota"

   ' Debug.Print "Fields after Delete"
   ' Debug.Print , "Type", "Size", "Name"


    ' Enumerate the Fields collection to show that the new
    ' fields have been deleted.
    For Each fldLoop In tdf.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop
   
    End If
   
  Next tdf

  dbs.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
    strCommand As String, strName As String, _
    Optional varType, Optional varSize)

    With tdfTemp

        ' Check first to see if the TableDef object is
        ' updatable. If it isn't, control is passed back to

        ' the calling procedure.
        If .Updatable = False Then
            MsgBox "TableDef not Updatable! " & _
                "Unable to complete task."
            Exit Sub
        End If

        ' Depending on the passed data, append or delete a
        ' field to the Fields collection of the specified
        ' TableDef object.
        If strCommand = "APPEND" Then
            .Fields.Append .CreateField(strName, _
                varType, varSize)
        Else
            If strCommand = "DELETE" Then .Fields.Delete _
                strName
        End If

    End With

End Sub




Klos, Susan wrote:

>I have an append query that can work for 71 tables.  I want to create a for
>next loop function that will substitute the table names (they are numbers)
>each time it loops.  Can someone help me?  I do not quite have the query def
>thing down and I think I need to use it here.  Each table has the same
>fields.
>
> 
>
>Susan Klos
>
>Senior Database Analyst
>
>Evaluation and Reporting
>
>Florida Department of Education
>
> 
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada