By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

How do I remotely add new field to table in external database?

P: n/a
I need a routine to add a new field to an existing table in a database
that is in another town.

Situation: I maintain a database with 10 copies in different offices.
Everytime we want to make a change or addition to a table's structure,
someone has to physically go out to each office and make the change.
Each database copy has it's own unique data, with tables links to
other tables within the database, so we can't just simply send a new
copy of the entire table or database. And it's not as simple as just
adding a field with the SQL's "Alter Table/Add Column" command, since
I need to set its properties at the same time which the SQL command
doesn't seem to allow.

Goal: I want to be able to email the update to the other offices, so
end users can run a simple routine that will update the database for
them.

For example, a new field needs to added to a Clients table with the
following properties:
Field Name: Gender
Data Type: Text
Field Size: 1 character
Input mask: >L
Display Control: Combo Box
Row Source Type: Value List
Row Source: "F";"M"

I've been researching a way to make table structure changes with a
routine of sorts, but not having much luck coming up with a solution.
Any help would be appreciated.

Apr 9 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Apr 9, 1:35 pm, kcop...@hotmail.com wrote:
I need a routine to add a new field to an existing table in a database
that is in another town.

Situation: I maintain a database with 10 copies in different offices.
Everytime we want to make a change or addition to a table's structure,
someone has to physically go out to each office and make the change.
Each database copy has it's own unique data, with tables links to
other tables within the database, so we can't just simply send a new
copy of the entire table or database. And it's not as simple as just
adding a field with the SQL's "Alter Table/Add Column" command, since
I need to set its properties at the same time which the SQL command
doesn't seem to allow.
In the past, I've seperated the database to a "Front End" and "Back
End" schema. Utilize the "Back End" (an Access Database) to house all
the data, while the "Front End" (another Access Database) points to
the back end to acquire the data.

This way, when you change the back end, you can update the front end
and copy it to their desktop without a loss of data. You can even
incorporate "version control" in which the front end will check the
version of the back end. This way the users can run a shortcut to
copy the newer version to their desktop.

I can provide some reference sites for implementing this.

OT

Apr 9 '07 #2

P: n/a
On Apr 9, 1:53 pm, "OdieTurbo" <todd.quig...@gmail.comwrote:
On Apr 9, 1:35 pm, kcop...@hotmail.com wrote:
In the past, I've seperated the database to a "Front End" and "Back
End" schema. Utilize the "Back End" (an Access Database) to house all
the data, while the "Front End" (another Access Database) points to
the back end to acquire the data.

This way, when you change the back end, you can update the front end
and copy it to their desktop without a loss of data. You can even
incorporate "version control" in which the front end will check the
version of the back end. This way the users can run a shortcut to
copy the newer version to their desktop.

I can provide some reference sites for implementing this.

OT
I'm sorry, your suggestion to split the database doesn't appear to
answer my question as to how to add a field to a database that is in
another remote location. Perhaps I wasn't very clear before: the 10
database copies are in remote office locations (different towns) that
are not networked, and each office only has dialup access to the
internet. I have a routine that works well for sending new versions of
forms, reports and queries by email (as they don't affect data), but
this time I need to update the table structure within each database
and I'm trying to save the hassle of having someone drive around for a
day visiting each of the 10 remote sites just to add a new field to
one of the tables. As I said before, it's not as simple as creating
one updated table and sending it out, as each of the 10 locations has
different data within their database that I do not want to distrub.

If you have reference sites on how to add a field to a table through
code or macro without distrubing the data already in the database,
that would be appreciated.

KC

Apr 11 '07 #3

P: n/a
On Apr 10, 11:07 pm, kcop...@hotmail.com wrote:
I'm sorry, your suggestion to split the database doesn't appear to
answer my question as to how to add a field to a database that is in
another remote location. Perhaps I wasn't very clear before: the 10
database copies are in remote office locations (different towns) that
are not networked, and each office only has dialup access to the
internet. I have a routine that works well for sending new versions of
forms, reports and queries by email (as they don't affect data), but
this time I need to update the table structure within each database
and I'm trying to save the hassle of having someone drive around for a
day visiting each of the 10 remote sites just to add a new field to
one of the tables. As I said before, it's not as simple as creating
one updated table and sending it out, as each of the 10 locations has
different data within their database that I do not want to distrub.

If you have reference sites on how to add a field to a table through
code or macro without distrubing the data already in the database,
that would be appreciated.

KC
Well, with that being said, you are correct, I had misunderstood the
situation. One possiblity might be to send a new make-table query
over that would copy the data to a temporary table, then another make-
table query to re-create the original table with the added columns.

Just my initial thought on the matter.

Apr 11 '07 #4

P: n/a
On 9 Apr, 18:35, kcop...@hotmail.com wrote:
I need a routine to add a new field to an existing table in a database
that is in another town.

Situation: I maintain a database with 10 copies in different offices.
Everytime we want to make a change or addition to a table's structure,
someone has to physically go out to each office and make the change.
Each database copy has it's own unique data, with tables links to
other tables within the database, so we can't just simply send a new
copy of the entire table or database. And it's not as simple as just
adding a field with the SQL's "Alter Table/Add Column" command, since
I need to set its properties at the same time which the SQL command
doesn't seem to allow.

Goal: I want to be able to email the update to the other offices, so
end users can run a simple routine that will update the database for
them.

For example, a new field needs to added to a Clients table with the
following properties:
Field Name: Gender
Data Type: Text
Field Size: 1 character
Input mask: >L
Display Control: Combo Box
Row Source Type: Value List
Row Source: "F";"M"

I've been researching a way to make table structure changes with a
routine of sorts, but not having much luck coming up with a solution.
Any help would be appreciated.

Apr 11 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.