# Solved: Adding a new Field to an existing table in Access using VB



## RKoch95 (Mar 21, 2011)

Hi! This is probably a pretty basic question, but I can't find an answer anywhere here or on the net. How do I add new fields, using VBA, to an existing table in Access? Without destroying thd data? My process: I initially import, using VBA, an Excel spreadsheet into a new table (this table is replaced each month by a new spreadsheet.) To facilitate my analysis of the data contained in that table, I need to add several fields to that existing table. Those fields will be used to store the results of several automated processes using that data. Because the table is created from the spreadsheet provided by another department each month, I need to add the new fields each time I import the spreadsheet. I am trying to do everything in VB because the final user needs to perform all functions by merely pushing buttons on a &quot;Switchboard&quot; My version is MSAccess2003. Thanks for your help!


----------



## OBP (Mar 8, 2005)

RKoch95, welcome to the Forum.
There are 2 ways to add fields to an existing table using VBA, one uses the TableDefs function and the other uses SQL.
However I would approach it in a different way, I would create the Table with all the fields that I want.
Import the Data to a Temporary table and then add the Records from the temporary table to the Main table using a simple Append Query.

The SQL adding a field method uses the Syntax
DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;"
in this case the table name is tbltest and the field name is Age.


----------



## RKoch95 (Mar 21, 2011)

OBP: Thanks for the solutions. The SQL solution looks simple and seems to fit my needs better. RK


----------



## OBP (Mar 8, 2005)

The BYTE can be replaced with the required size of the field.
DoCmd.RunSQL "ALTER TABLE Nghich ADD COLUMN [HeHe] (255);"


----------



## RKoch95 (Mar 21, 2011)

OBP: So, if I needed the field to hold a double integer I would do something like: DoCmd.RunSQL "ALTER TABLE Nghich ADD COLUMN [HeHe] DOUBLE;" is that right? Thanks for your help. RK


----------



## OBP (Mar 8, 2005)

I am not sure, as I don't use it much.


----------



## RKoch95 (Mar 21, 2011)

Okay ... I can only try it ... I'll let you know the result!! Thanks RK


----------



## RKoch95 (Mar 21, 2011)

OBP: Okay ... that works. Using the "double" definition added a the [HeHe] field to my table and defined it as a NUMBER/DOUBLE. Excellent!! thanks, RK (How to i get this editor to recoginize my control/line feeds and keep my formatting??!!)


----------



## OBP (Mar 8, 2005)

What "Editor"?


----------



## RKoch95 (Mar 21, 2011)

Oh, sorry. This window, where we type our messages, is often referred to as an "editor window" or "editor" for short. In "MY Account" preferences there is a section called "EDIT OPTIONS". In there we can set the "MESSAGE EDITOR INTERFACE" option to full WYSIWYG. Even though I've selected FULL WYSIWYG as my preference, whenever I post a message the editor strips out all my carriage returns and line feeds and jams everything together in one long paragraph. Makes me look illiterate. Sorry, I'll send a note to one of the administrators to see what I'm doing wrong ... really not appropriate here. Again, Thanks for your help. Using the solutions you suggested has allowed me to move forward with my project. -RK


----------



## RKoch95 (Mar 21, 2011)

All:

Got this from a friend of mine. It represents another solution for adding columns(fields) to an existing table. Although I've defined 5 new fields here, you can use it to add more or less than that depending on your needs:

========================================================================
Private Sub AddColumn()
Dim curDatabase As Database
Dim tblTooAddToo As TableDef
Dim colFullName1 As Field
Dim colFullName2 As Field
Dim ColFullName3 As Field
Dim colFullName4 As Field
Dim colFullName5 As Field

' Get a reference to the current database
Set curDatabase = CurrentDb

' Get a reference to a table named TestTable - NOTE: The table MUST exist
Set tblTooAddToo = curDatabase.TableDefs("TestTable")

'define the fields using the CreateField method
' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
' I used the predefined types already in Access
Set colFullName1 = tblTooAddToo.CreateField("Original_Code", dbText, 12)
Set colFullName2 = tblTooAddToo.CreateField("Type_Code", dbText, 2)
Set ColFullName3 = tblTooAddToo.CreateField("PreAuth_Code", dbDouble)
Set colFullName4 = tblTooAddToo.CreateField("Description", dbText, 10)
Set colFullName5 = tblTooAddToo.CreateField("Applied_For", dbText, 10)

'use the APPEND method to add the fields to the table
With tblTooAddToo.Fields
.Append colFullName1
.Append colFullName2
.Append ColFullName3
.Append colFullName4
.Append colFullName5
End With

End Sub
===============================================================

=RK


----------

