Adding field in backend database

  • Thread starter Thread starter ReidarT
  • Start date Start date
R

ReidarT

I want to add a field in a back-end database.
I want to do it by code from front-end-database (currently open)
regards
reidarT
 
What you ask can be done, but it's very rarely a good idea.

Instead of a "broad, flat" table, with an ID and multiple columns, consider
a "long, narrow" table, with ID, columnname, and value.
That way you can add new columnames without having to add fields to the
table.

HTH
- Turtle
 
Thanks, but I need to do it this way. There is one backend database on each
office with lots of data, and I need to add the new field from the new
version of the front-end database.
regards
reidarT
 
Just open a direct reference to the back end database table:

dim db as database, td as tabledef, fld as field
set db = dbengine.opendatabase ("path to BE database")
set td = db.tabledefs![TheTable]
set fld = td.createfield (...)

etc, as in online help for CreateField. Remember you have to append the
newly-created field object, to the tabledef's Fields collection, as per the
help examples. You can do all this while both db's are open.

HTH,
TC
 
Hi Reidar,

Based on my understanding, you could do it by execute T-SQL alter table
statement directly from the client application. For instance, if you are
using ADO, you could use ADOX to realize it. You could get examples from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
admscadoxfundamentals.asp?frame=true

Also, you could using SQL-DMO to add it, the following document will show
you an example:
Altering a Table by Adding a Column
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmor
ef_ex02_92ep.asp

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Thanks, this seems to work OK when creating a new table, I just want to add
a new field in an existing table.
regards
reidarT
TC said:
Just open a direct reference to the back end database table:

dim db as database, td as tabledef, fld as field
set db = dbengine.opendatabase ("path to BE database")
set td = db.tabledefs![TheTable]
set fld = td.createfield (...)

etc, as in online help for CreateField. Remember you have to append the
newly-created field object, to the tabledef's Fields collection, as per the
help examples. You can do all this while both db's are open.

HTH,
TC


ReidarT said:
Thanks, but I need to do it this way. There is one backend database on each
office with lots of data, and I need to add the new field from the new
version of the front-end database.
regards
reidarT
Click to expand...
Click to expand...
 
Thanks, this seems to work OK when creating a new table, I just want to add
a new field in an existing table.
regards
reidarT
 
What I suggested is specifically designed for adding to an existing table
:-)

If the table did not already exist, the 'set td=' line would fail.

Adding new fields via DAO is a bit tricky to get right. Maybe get it working
on a local table first (using the examples for CreateField in online help).
You could do that by replacing my 'set db=' line with 'set db =
currentdb()'. Then, when you've got it working locally, change that one line
back.

HTH,
TC
(off for 2 days)


ReidarT said:
Thanks, this seems to work OK when creating a new table, I just want to add
a new field in an existing table.
regards
reidarT
TC said:
Just open a direct reference to the back end database table:

dim db as database, td as tabledef, fld as field
set db = dbengine.opendatabase ("path to BE database")
set td = db.tabledefs![TheTable]
set fld = td.createfield (...)

etc, as in online help for CreateField. Remember you have to append the
newly-created field object, to the tabledef's Fields collection, as per the
help examples. You can do all this while both db's are open.

HTH,
TC


ReidarT said:
Thanks, but I need to do it this way. There is one backend database
Click to expand...
on
each
office with lots of data, and I need to add the new field from the new
version of the front-end database.
regards
reidarT
"MacDermott" <[email protected]> skrev i melding
What you ask can be done, but it's very rarely a good idea.

Instead of a "broad, flat" table, with an ID and multiple columns,
consider
a "long, narrow" table, with ID, columnname, and value.
That way you can add new columnames without having to add fields to the
table.

HTH
- Turtle

I want to add a field in a back-end database.
I want to do it by code from front-end-database (currently open)
regards
reidarT
Click to expand...
Click to expand...
Click to expand...
 
Hi Reidar,

I am glad to hear that you could make it OK when creating a new table, do
you still have problems adding a new field? If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
By any chance is your table open (e.g. a form using it is open) when you run
this code?
You can't change the structure of a table while any connection to it is
open.

HTH
- Turtle
 
Back
Top