Results 1 to 9 of 9
  1. #1
    audave is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    4

    VBA DAO code to Loop through tables and do tasks with each table

    I have a variety of tasks to do with each table, e.g. add new fields, populate some fields, set up indexes etc.
    That's all, I just want to loop through each table, I'm just not confident enough myself to do it yet. I just need a bit of code to get started. Thanx.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    normally, you dont add fields. Not often enough that you need a routine to do it.
    You build the table and add records.

    This could be a design flaw, What fields are you wanting to add?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    audave is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    4
    I understand your concern, I have a group of tables that are temporary and need to be re-created periodically, so it would be convenient to have the code. I have some knowledge of DAO but I will have a read of Allen's code, after all he is an Aussie.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could also have some table structures eg DummyTbl1, DummyTbl2 and copy these structures and give the new copies proper names when you need them.
    Or copy the table structure after you have it the first time, and just recopy that structure when it is required.
    Or use a vba routine to execute some SQL DDL to recreate and name the tables when needed.

    It isn't clear (to me) what you really need, nor how it could/should be impemented.
    I'm not sure why it has to be DAO specifically. However, Allen (the Aussie) is a most respected reference.

    Good luck.

  6. #6
    audave is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    4
    What seems to be clear is that I'm not going to get my question answered. Data Access Objects is one technology that is available, I chose to learn it over ADO. I've followed up on the lead you've given, so that may be it.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What seems to be clear is that I'm not going to get my question answered.
    Perhaps you could restate the question for clarity.

    Creating a Table using DAO and vba directly from the Allen Browne reference given earlier.

    Code:
    Option Compare Database
    Option Explicit
    
    'Constants for examining how a field is indexed.
    Private Const intcIndexNone As Integer = 0
    Private Const intcIndexGeneral As Integer = 1
    Private Const intcIndexUnique As Integer = 3
    Private Const intcIndexPrimary As Integer = 7
    
    Function CreateTableDAO()
        'Purpose:   Create two tables using DAO.
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        'Initialize the Contractor table.
        Set db = CurrentDb()
        Set tdf = db.CreateTableDef("tblDaoContractor")
        
        'Specify the fields.
        With tdf
            'AutoNumber: Long with the attribute set.
            Set fld = .CreateField("ContractorID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            'Text field: maximum 30 characters, and required.
            Set fld = .CreateField("Surname", dbText, 30)
            fld.Required = True
            .Fields.Append fld
            
            'Text field: maximum 20 characters.
            .Fields.Append .CreateField("FirstName", dbText, 20)
            
            'Yes/No field.
            .Fields.Append .CreateField("Inactive", dbBoolean)
            
            'Currency field.
            .Fields.Append .CreateField("HourlyFee", dbCurrency)
            
            'Number field.
            .Fields.Append .CreateField("PenaltyRate", dbDouble)
            
            'Date/Time field with validation rule.
            Set fld = .CreateField("BirthDate", dbDate)
            fld.ValidationRule = "Is Null Or <=Date()"
            fld.ValidationText = "Birth date cannot be future."
            .Fields.Append fld
            
            'Memo field.
            .Fields.Append .CreateField("Notes", dbMemo)
            
            'Hyperlink field: memo with the attribute set.
            Set fld = .CreateField("Web", dbMemo)
            fld.Attributes = dbHyperlinkField + dbVariableField
            .Fields.Append fld
        End With
        
        'Save the Contractor table.
        db.TableDefs.Append tdf
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print "tblDaoContractor created."
        
        'Initialize the Booking table
        Set tdf = db.CreateTableDef("tblDaoBooking")
        With tdf
            'Autonumber
            Set fld = .CreateField("BookingID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            'BookingDate
            .Fields.Append .CreateField("BookingDate", dbDate)
            
            'ContractorID
            .Fields.Append .CreateField("ContractorID", dbLong)
            
            'BookingFee
            .Fields.Append .CreateField("BookingFee", dbCurrency)
            
            'BookingNote: Required.
            Set fld = .CreateField("BookingNote", dbText, 255)
            fld.Required = True
            .Fields.Append fld
        End With
        
        'Save the Booking table.
        db.TableDefs.Append tdf
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print "tblDaoBooking created."
        
        'Clean up
        Application.RefreshDatabaseWindow   'Show the changes
        Set fld = Nothing
        Set tdf = Nothing
        Set db = Nothing
    End Function
    Here is a link to the DAO Object model

    Here is code to loop through all tabledefs in the current database and print the names to the immediate window.
    Code:
    Sub showTableNames()
    Dim tdf As DAO.TableDef
     For Each tdf In CurrentDb.TableDefs
       Debug.Print tdf.name
     Next tdf
    End Sub

    Here is code to loop through a table's Fields using DAO and print the Field names to the immediate window..

    Code:
    For i = 0 to db.TableDefs(0).Fields.Count - 1
       Debug.print db.TableDefs(0).Fields(i).Name
    Next

  8. #8
    audave is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    4
    It would have been helpful to have posted this code earlier in our discourse, that is all I asked for. I think we can end it off here, thanks for your help.

  9. #9
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by audave View Post
    It would have been helpful to have posted this code earlier in our discourse, that is all I asked for. I think we can end it off here, thanks for your help.
    Sorry for your frustrating because you missed his original link to the page that has everything you need for DAO and tables.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  2. Replies: 6
    Last Post: 01-26-2015, 02:11 PM
  3. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  4. Multiple tasks in vb code
    By JFo in forum Programming
    Replies: 8
    Last Post: 10-03-2011, 07:58 PM
  5. How to loop code to run through entire table
    By kmajors in forum Reports
    Replies: 9
    Last Post: 04-23-2010, 09:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums