cannot execute data definition statements on linked data sources

Hi Group,

I have two databases, one containing the code and another containing all the
data tables. The first database links to tables in the data database.
I need to update the structure of a few tables in the data database and it
cannot be done because the tables are linked (see the message subject). For
example, add some new fields in a table, add a constraint, a foreign key.

I found a way of updating the structure by creating a new empty data
database with the new structure and then importing the data from the old
data file to the new one.
But this is often a lengthy operation because of the size of the data files
and I would prefere a simpler method for the customers.

Is there any other way of doing that ?

Thank you for any suggestions !

Andrei.

andrei wrote:
Hi Group,

I have two databases, one containing the code and another containing all
the
data tables. The first database links to tables in the data database.
I need to update the structure of a few tables in the data database and it
cannot be done because the tables are linked (see the message subject).
For
example, add some new fields in a table, add a constraint, a foreign key.

I found a way of updating the structure by creating a new empty data
database with the new structure and then importing the data from the old
data file to the new one.
But this is often a lengthy operation because of the size of the data
files
and I would prefere a simpler method for the customers.

Is there any other way of doing that ?

Thank you for any suggestions !

Andrei.

IF I understand what you have and want, all you need do is to open the
backend database (you call it a data database) and make the table changes
there.

The general idea is to have the dynamic data (tables of data that
changes) in the back end and generally access it from the front-end
databases on each user's machine, with all the forms, reports queries and
static data. To make changes to structure you need to open the backend
database. You may need to open it "exclusively" to keep others from linking
to it while you make changes.

Note: I suggest that you make a copy of that backend database and move
it to a development area and work on it there. Test it out with copies of
the front end(s) to assure they do not need to be adjusted before moving it
into production.


--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Hi Joseph

Thank you for your prompt response.

My problem with this solution is that the application (frontend and backend)
are both deployed at customer sites, so I would need to obtain their backend
database and update it myself. That's exactly what I'm trying to avoid,
because of the size of the backend.

Andrei.






"Joseph Meehan" wrote in message
...
andrei wrote:
Hi Group,

I have two databases, one containing the code and another containing all
the
data tables. The first database links to tables in the data database.
I need to update the structure of a few tables in the data database and
it
cannot be done because the tables are linked (see the message subject).
For
example, add some new fields in a table, add a constraint, a foreign
key.

I found a way of updating the structure by creating a new empty data
database with the new structure and then importing the data from the old
data file to the new one.
But this is often a lengthy operation because of the size of the data
files
and I would prefere a simpler method for the customers.

Is there any other way of doing that ?

Thank you for any suggestions !

Andrei.

IF I understand what you have and want, all you need do is to open the
backend database (you call it a data database) and make the table changes
there.

The general idea is to have the dynamic data (tables of data that
changes) in the back end and generally access it from the front-end
databases on each user's machine, with all the forms, reports queries and
static data. To make changes to structure you need to open the backend
database. You may need to open it "exclusively" to keep others from
linking
to it while you make changes.

Note: I suggest that you make a copy of that backend database and move
it to a development area and work on it there. Test it out with copies of
the front end(s) to assure they do not need to be adjusted before moving
it
into production.


--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

"andrei" wrote in message
...
Hi Joseph

Thank you for your prompt response.

My problem with this solution is that the application (frontend and
backend)
are both deployed at customer sites, so I would need to obtain their
backend
database and update it myself. That's exactly what I'm trying to avoid,
because of the size of the backend.

You don't understand. Your front end file can execute code that will "open"
the back end file and then issue the changes. Then you just use more code
to refresh the links so that the front end file sees the changes made.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Thanks Rick for answering.

My problem, as the subject of this message states, is that if I try to run
an sql statement against a linked table, I get an error.
This only happens with DDL statements. The DML statements work fine.

And the application IS actually using code to dynamically refresh the links
to the backend.

So, my question is : how do I issue an "alter table T1 add c1 number" from
the frontend, when T1 is a linked table from the backend.

Thanks !

Andrei.



"Rick Brandt" wrote in message
...
"andrei" wrote in message
...
Hi Joseph

Thank you for your prompt response.

My problem with this solution is that the application (frontend and
backend)
are both deployed at customer sites, so I would need to obtain their
backend
database and update it myself. That's exactly what I'm trying to avoid,
because of the size of the backend.

You don't understand. Your front end file can execute code that will
"open"
the back end file and then issue the changes. Then you just use more code
to refresh the links so that the front end file sees the changes made.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"andrei" wrote in message
...
Thanks Rick for answering.

My problem, as the subject of this message states, is that if I try to run
an sql statement against a linked table, I get an error.

I didn't suggest that you run DDL against a link. You need to open an instance
of the back end database in code. You have to understand that the *tables* do
not exist in the front end file, only a link does. If you want to modify the
tables "in the back end file" then you need to open that file, but you can do so
from within the front end file.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com