CODEWITHSUNDEEP
vbams-access
user9799161
user9799161

Reputation: 125

Access VBA - add new column to existing table with name of the actual table

I have imported various tables - WIP_100_AGR_USERS, WIP_100_USR06, WBP_100_USR02 with their real names as they were downloaded from SAP. Now I would need to add column "SYSTEM_NO"where would be the name of the table by use of VBA for ACCESS next to all other records in the particular table.

E.g. I have table WIP_100_USR06, so the result will be:

AGR NAME| SYSTEM NO
________|______________
ashkjas | WIP_100_USR_06
asdjhjh | WIP_100_USR_06

Thank you!!!

Upvotes: 0

Views: 2778

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

Providing none of your tables already have a field named SYSTEM NO then this should work: 

Sub InsertNameField()

    Dim tdf As DAO.TableDef

    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            CurrentDb.Execute "ALTER TABLE [" & tdf.Name & _
                "] ADD COLUMN [SYSTEM NO] TEXT(255)", dbFailOnError

            CurrentDb.Execute "UPDATE [" & tdf.Name & _
                "] SET [SYSTEM NO] = '" & tdf.Name & "'", dbFailOnError

        End If
    Next tdf

    MsgBox "Finished."

End Sub

NB Test on a copy of your database first.

Upvotes: 2

Related Questions