Hi all



I am trying to use the alter table command to add a column and can't get the

syntax. This is actually been done in ASP via ADO but I suppose the syntax

should be roughly the same.



The command I am using is



Alter table vehicles add column new_price decimal(10,2);

which fails

Alter table vehicles add column new_price decimal

works though but creates a field of size auto.



I'd really appreciate it if someone could tell me what is going on!



tia

Regards



Graham Brown

Hi Graham,

This should work for you.



Usage:

     fAddFld("TargetTable","NewFieldName", iType,"Q:\Data\Other.MDB")



Note that parameter 4 is optional - used to reference either mapped or UNC

path defined database file specification.  If blank, the function assumed

the

current database.





Function fAddFld(sTbl As String, sFld As String, iTyp As Integer, _

                           Optional sRemote As String) As Long

' Parm 1: sTbl         (String)name of table to append field to

' Parm 2: sFld         (String)name of field to append

' Parm 3: iTyp         (Integer) or DAO DataType type for new field(if text

-

watch default size...)

' Parm 4: sRemote   [Optional] (String) Path and name of database to modify

' Types are: dbBigInt, dbBinary, dbBoolean, dbByte, dbChar, dbCurrency,

dbDate, dbDecimal,

'                  dbDouble, dbFloat, dbGuid, dbInteger, dbLong,

dbLongBinary,

dbMemo, dbNumeric,

'                  dbSingle,dbText, dbTime, dbTimeStamp, dbVarBinary



Dim wsp As Workspace, dbs As Database, tdf As TableDef

  

  If Len(sRemote & "") = 0 Then                  ' Default is current

database

    Set dbs = DBEngine(0)(0)                     ' Faster call syntax than

CurrentDB

  Else                                            ' Define and open the

Remote

database

    Set wsp = DBEngine.Workspaces(0)

    Set dbs = wsp.OpenDatabase(sRemote, False)

  End If



  Set tdf = dbs.TableDefs(sTbl)                  ' Error if Not Found,

Locked,

Permission...

  With tdf                                        ' Use 'with' for multiple

field actions

    .Fields.Append .CreateField(sFld, iTyp)  ' Append Field

    .Fields.Refresh

  End With

  dbs.TableDefs.Refresh                           ' Refresh the list

  fAddFld = Err.Number                            ' How did it go

  

  Set wsp = Nothing

  Set dbs = Nothing

  Set tdf = Nothing

  

End Function





Tom Hamilton

T_Systems, Inc

Database Programmer