creating fields for table

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I'm am using:
Dim tdf As DAO.TableDef
Set tdf = db.CreateTableDef("MyNewTable")

I am having trouble creating the fields for "MyNewTable".
I have the fields stored as records in another table. How
do I set them up?

Thanks.
Rick
 
Here's an example of creating a table with one field:

'**************************************************

Sub exaCreateTable()
'DAO DDL example
'demonstrates creating a table, fields, properties
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field

' Create the table and a field
Set db = CurrentDb
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("NewField", dbText, 100)

' Set field properties
fld.AllowZeroLength = True
fld.DefaultValue = "Unknown"
fld.Required = True
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"

' Append field to Fields collection
tblNew.Fields.Append fld

' Append table to TableDef collection
db.TableDefs.Append tblNew

End Sub
'**************************************************
Appending the Fields to the Tabledef and the Tabledef to the Tabledefs
Collection are key here.

For more examples, download this file:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=DAO.mdb
And check out the "Chapter 11" and "Chapter 13" modules.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
code below works fine at me:
Set tdf = dbsData.CreateTableDef(rst!UpdateTable)
Set fld = tdf.CreateField(rst!UpdateField,
rst!UpdateValue1, rst!UpdateValue2)
fld.DefaultValue = rst!UpdateValue3
tdf.Fields.Append fld
tdf.Fields.Refresh
dbsData.TableDefs.Append tdf
dbsData.TableDefs.Refresh
Set tdf = Nothing

instead of UpdateTable, UpdateField, UpdateValue1, etc - use your own field
names
 
Back
Top