New field old table

[cbiker]

Is it possable to add a new field to an old table with information in it (and not loose information)using code.
I need to add a field named units to a table named bsd in a data base that is at another location I would like to send them a new Front end that would when first started would check to see if the field was there if not would add it to the table in the back end named Pdata.
Thanks for any help.

 

[danvlas]

Paste this function in a module (keep in mind it is DAO oriented, so you need a reference to DAO)

Function fInsertField(DbName As String, TblName As String, FldName As String, FldType, FldSize As Integer) As Boolean

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim fld As New dao.Field

Set db = DBEngine.OpenDatabase(DbName)
On Error GoTo ErrHandler
Set tdf = db.TableDefs(TblName)
fld.Name = FldName
fld.Type = FldType
fld.Size = FldSize
tdf.Fields.Append fld
fInsertField = True
ExitHere:
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandler:
'3191
If Err.Number = 3191 Then
fInsertField = True
Else
MsgBox Err.Number & Err.Description
End If
Resume ExitHere
End Function

Alternatively, you can use an ALTER TABLE statement on the remote database:

db.Execute "ALTER TABLE [" & TblName & "] ADD COLUMN [" & fldName & "] & FldType & "(" & FldSize & &quot"

This will work in ADO as well, provided you replace DAO.Database wit ADODB.Connection and open it properly.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html