Hi, Eyal.
i am looking for a code for appending new field (dynamical) to an existing
table
In general, one doesn't dynamically add fields to tables at runtime --
unless the table isn't normalized. If you're doing this to speed up initial
development time or later maintenance for upgrading or expanding an
application, then this capability would be appropriate. So ensure that
you're not adding a new field such as "Date2," "Date3," or other repeats of
fields that you've already created for the table.
It's easier to add a new field using SQL, but it's also easy to do with the
DAO library, and I'm sure you can find some other examples with ADOX if you
would rather avoid either of these two examples:
Public Sub addColToTbl()
On Error GoTo ErrHandler
CurrentDb().Execute "ALTER TABLE tblMyTable " & _
"ADD COLUMN OrgName Text (25) NOT NULL;", _
dbFailOnError
Exit Sub
ErrHandler:
MsgBox "Error in addColToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Using the DAO library:
Public Sub addFieldToTbl()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
Set tbl = db.TableDefs("tblMyTable")
Set fld = tbl.CreateField("OrgName", dbText, 25)
tbl.Fields.Append fld
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False
CleanUp:
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in addFieldToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
Either of these examples adds a Text data type field of 25 characters to an
existing table, with the "Required" Property set to Yes.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
