Hey Happy, i know these posts start annoying you, but changing database structure from the front-end is not so highly recommended.
Anyway, if you want to update the db structure from the front end application you should use:
Dim db as Database
Set db=OpenDatabase(strDbName)
db.Execute "ALTER TABLE... "
as Keith wrote.
However, the db.execute doesn't provide a common solution since db.Execute proceeds Access Jet commands, that doesn't correspond the full list of standart sql commands (supported by ms sql server at least). For instance, you can not define cascades in relations, change primary keys, etc...
These issues are resolved with ADO or DAO technolgies.
For DAO, addition of columns works something like:
Dim db as DAO.Database
Dim tDef as DAO.TableDef
Set db=OpenDatabase(strDbName)
Set tDef=db.TableDefs(strTableName)
With tDef
.Fields.Append .CreateField(strFieldName, [dbDouble/dbLong/etc])
End With
Set tDef=Nothing
Set db=Nothing
In addition if you don't want to use constant strDbName (i.e different tables linked to different sources) you can find out it by:
Public Function GetLinkedDBName(TableName As String)
Dim db As DAO.Database, Ret
On Error GoTo DBNameErr
Set db = CurrentDb()
Ret = db.TableDefs(TableName).Connect
GetLinkedDBName = Right(Ret, Len(Ret) - (InStr(1, Ret, "DATABASE=") + 8))
Exit Function
DBNameErr:
GetLinkedDBName = 0
End Function
For this you need to add a refference to DAo 3.51/3.6 in your project
Good Luck Happy!
