Add a field to a table using VBA

Dear All,

How can I add a field to the end of my table using a VBA script?

Thanks.
saulius88AnalystAsked:
Who is Participating?
 
sbiddleCommented:
This should do it!

Dim db as database
dim found as boolean

set db = currentdb

i = 0
while not i = db.tabledefs("MyTable").fields.count - 1
  if  db.tabledefs("MyTable").fields(i) = "MyColumn" then
    found = true
    i = db.tabledefs("MyTable").fields.count - 1
  else
    found = false
    i = i + 1
  end if
wend

if found <> true then
  strSQL = "ALTER TABLE MyTable ADD Column MyColumn Text(20)"
  Currentdb.Execute strSQL
end if
0
 
NatchiketCommented:
you can use a  DDL string

.e.g.

Dim strSQL as String
strSQL = "ALTER TABLE MyTable ADD Column MyColumn Text(20)"
Currentdb.Execute strSQL


Where MyTable is the name of your table and MyColumn is the name of your field
You will have to change Text(20) depending on the data type and size of the field you want
0
 
saulius88AnalystAuthor Commented:
Thanks for the post. Is there any way I could also check if the field already exists?
0
 
saulius88AnalystAuthor Commented:
Thanks a bunch.
0
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.

All Courses

From novice to tech pro — start learning today.