MS Access - Add new field to multiple tables

Hi guys, I have an Access 2010 database with about 90 identical tables and need to add a new field to all of them. Is there a way I can add the new field to all of them at once or do I have to manually go into each table one by one and add the new field?
LVL 1
amaru96Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Sub Demo()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
            ' Add text field
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewTextColumn text(256)", dbFailOnError 
           ' Add boolean field
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewBooleanColumn Bit not null", dbFailOnError 
           ' Add Date/time field
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewDateTimeColumn datetime null", dbFailOnError 
           ' Add memo field
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewMemoColumn memo null", dbFailOnError 
           ' Add Long field
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewLongColumn Long not null", dbFailOnError 
           ' Add autonumber
            CurrentDb.Execute "ALTER TABLE " & tdf.name & " ADD COLUMN NewautonumberColumn Long NOT NULL IDENTITY(1,1)", dbFailOnError 
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amaru96Author Commented:
That worked great! Thanks for that.

I have another db that I need to do the same thing to, however I only want it to add the new field to table names containing the word "details". Is there a way to modify that code to do that?

For example, I have:
Table1
Table1_details
Table2
Table2_details
0
MacroShadowCommented:
Sure:
If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
    If inStr(tdf.name, "details") > 1 Then
    ....
    End If
End If

Open in new window

0
amaru96Author Commented:
Awesome! Saved me a lot of time. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.