adding additional fields to BE database

[LJG]

Hi Guys,

Anyone have a way of adding additional fields to a BE database that has been
deployed to a number of users.

I have no problem creating a copy of the data to temptables, but need to
when I try to do a make table. As my current process I get an error as the
table is part of a number of relationships.

Any help would be appreciated

TIA
Les

 

[Tim Ferguson]

Anyone have a way of adding additional fields to a BE database that
has been deployed to a number of users.

The easiest way to do this is to write a DDL script; something like

dim db as dao.database
dim jetSQL as string


' open it exclusively; make sure other users are not using it
' check the other parameters before using this!!
set db = opendatabase(strPathToBEDatabase, true, false)

' create the commands
jetSQL = "ALTER TABLE One " & _
"ADD COLUMN MyNewInteger NOT NULL " & _
"CONSTRAINT FOREIGN KEY REFERENCES Second(SecondID) "
db.Execute jetSQL, dbFailOnError

' if you want to set some properties you have to use
' the ADO execute method instead or else get into the
' DAO collections.
' as above, this is air code and needs checking!
with db.Tabledefs("One").Fields("MyNewInteger")
.createproperty("DefaultValue", dbInteger, 121)

' all done?
db.Close


You can distribute an mdb with just this procedure attached to the
Autoexec macro, so they just open it once and delete it. Alternatively
you can put the whole lot in a vbs script and get the local admin to run
that instead. Don't forget that the customer site will need to be able to
test the new backend along with the new front end.

Hope that helps


Tim F

 

[LJG]

Cheers, thanks for that
Les

The easiest way to do this is to write a DDL script; something like

dim db as dao.database
dim jetSQL as string


' open it exclusively; make sure other users are not using it
' check the other parameters before using this!!
set db = opendatabase(strPathToBEDatabase, true, false)

' create the commands
jetSQL = "ALTER TABLE One " & _
"ADD COLUMN MyNewInteger NOT NULL " & _
"CONSTRAINT FOREIGN KEY REFERENCES Second(SecondID) "
db.Execute jetSQL, dbFailOnError

' if you want to set some properties you have to use
' the ADO execute method instead or else get into the
' DAO collections.
' as above, this is air code and needs checking!
with db.Tabledefs("One").Fields("MyNewInteger")
.createproperty("DefaultValue", dbInteger, 121)

' all done?
db.Close


You can distribute an mdb with just this procedure attached to the
Autoexec macro, so they just open it once and delete it. Alternatively
you can put the whole lot in a vbs script and get the local admin to run
that instead. Don't forget that the customer site will need to be able to
test the new backend along with the new front end.

Hope that helps


Tim F

 

[Pat Hartman\(MVP\)]

Don't do it while others have the database open. This type of change should
only be made when the database is "off line".