cannot execute data definition statements on linked data sources

[andrei]

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.

 

[Joseph Meehan]

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.

 

[andrei]

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.

 

[Rick Brandt]

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.

 

[andrei]

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]

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.