[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Add a field to a table using VBA

Posted on 2006-04-10
4
Medium Priority
?
584 Views
Last Modified: 2013-04-10
Dear All,

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

Thanks.
0
Comment
Question by:saulius88
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Natchiket
ID: 16424002
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
 

Author Comment

by:saulius88
ID: 16424094
Thanks for the post. Is there any way I could also check if the field already exists?
0
 
LVL 3

Accepted Solution

by:
sbiddle earned 2000 total points
ID: 16424645
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
 

Author Comment

by:saulius88
ID: 16424656
Thanks a bunch.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question