Addinf firelds to tables remotely

[Scott]

Is there a way to add fields to a table from a query?

I have a database with back end tables and if I make changes to the front
end that include adding extra fields to a table, I have to trust the user to
update the tables. Quite often this may just be the addition of a Yes/No
tick box or something similar. Obviously if they don't quite do it right,
the updated front end won't work.

I want to be able to perhaps add a query or something that will allow the
user to do an update of the front end, press a buton on a form that will
activate a query to update the tables with what I want.

Thanks in Advance for any assistance.

[Wayne Gillespie]

On Sat, 11 Feb 2006 15:14:42 +0800, "Scott" <sc********@iinet.net> wrote:

Is there a way to add fields to a table from a query?

I have a database with back end tables and if I make changes to the front
end that include adding extra fields to a table, I have to trust the user to
update the tables. Quite often this may just be the addition of a Yes/No
tick box or something similar. Obviously if they don't quite do it right,
the updated front end won't work.

I want to be able to perhaps add a query or something that will allow the
user to do an update of the front end, press a buton on a form that will
activate a query to update the tables with what I want.

Thanks in Advance for any assistance.

You can do this with an ALTER TABLE query (see help for details) but I generally
use DAO for this purpose -

Function fAddNewFieldToLinkedTable(strTableName As String, strPKey As String, _
strfieldname As String, intFieldType As Integer, Optional intFieldSize As
Integer, Optional vDefVal As Variant)
'opens a linked table directly and adds new field to seleted table

Dim strBEPath As String
Dim Bfdb As Database
Dim strSQL As String
Dim rst As Recordset
Dim vChk As Variant
Dim tdf As TableDef
Dim fld As Field

On Error GoTo HandleIt

strBEPath = CurrentDb().TableDefs(strTableName).Connect
strBEPath = Right(strBEPath, Len(strBEPath) - 10)

Set Bfdb = CurrentDb()
strSQL = "SELECT * FROM " & strTableName & " WHERE ((" & strPKey & ")=" &
DMin(strPKey, strTableName) & ");"
Set rst = Bfdb.OpenRecordset(strSQL, dbOpenSnapshot)
With rst
If .RecordCount > 0 Then
vChk = .Fields(strfieldname)
vChk = True
End If
.Close
End With

AddField:
If vChk = False Then
Set Bfdb = Nothing
Set Bfdb = DBEngine(0).OpenDatabase(strBEPath)
Set tdf = Bfdb.TableDefs(strTableName)
Set fld = tdf.CreateField(strfieldname, intFieldType, intFieldSize)
If Not IsMissing(vDefVal) Then
fld.DefaultValue = vDefVal
End If
tdf.Fields.Append fld
End If
OutHere:
If Not (fld Is Nothing) Then Set fld = Nothing
If Not (tdf Is Nothing) Then Set tdf = Nothing
If Not (rst Is Nothing) Then Set rst = Nothing
If Not (Bfdb Is Nothing) Then Set Bfdb = Nothing
Exit Function

HandleIt:
Select Case Err
Case 0
Resume Next
Case 3265 'field does not exist
rst.Close
vChk = False
Resume AddField
Case Else
Beep
MsgBox Err & " " & Err.Description, vbCritical + vbOKOnly, "Error
adding new field to table"
Resume OutHere
End Select

End Function

Wayne Gillespie
Gosford NSW Australia