ODBC and linked tables 
Author Message
 ODBC and linked tables

I have a program that uses ODBC to read data from an Access97 database.
For a future upgrade I'd like to split the database into two separate
files with one file containing links to some of the tables of the other
file.
Problem: my program does not 'see' the linked tables at all. Is this a
testing error? I.e.: can it be done at all? Or should I forget about it?

A secondary problem is that I do not really know much about ODBC. Could
anyone please suggest some good books and/or websites on ODBC?

--
Greetings from
 _____
 /_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
        Remove SPAMBLOCK from my address ...



Fri, 08 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Auke,
The ODBC data source can be viewed/set/changed in the control panel- ODBC
applet.
It is neccessary for you to change this, when you split the db, if the
original db name is no longer valid.
I would have thought that the ODBC source could point to your front end
(linked) db, but I could be wrong.
You will have to address this in any install routines of your program.
Is the 'Program' something you have written?
If so have you considered accessing the data directly (ADO/DAO etc) instead
of ODBC?

Just some thoughts,
Jeff Davies


Quote:
> I have a program that uses ODBC to read data from an Access97 database.
> For a future upgrade I'd like to split the database into two separate
> files with one file containing links to some of the tables of the other
> file.
> Problem: my program does not 'see' the linked tables at all. Is this a
> testing error? I.e.: can it be done at all? Or should I forget about it?

> A secondary problem is that I do not really know much about ODBC. Could
> anyone please suggest some good books and/or websites on ODBC?

> --
> Greetings from
>  _____
>  /_|__| Auke Reitsma, Delft, The Netherlands.
> /  | \  -------------------------------------
>         Remove SPAMBLOCK from my address ...



Sun, 10 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
On Wed, 24 May 2000 14:34:06 +1000, "Jeff Davies"

Quote:

> Auke,

Hi Jeff,

Quote:
> The ODBC data source can be viewed/set/changed in the control panel- ODBC
> applet.

Yeah, I know. But in this case it is not appropriate.

Quote:
> It is neccessary for you to change this, when you split the db, if the
> original db name is no longer valid.

Maybe I wasn't clear enough. The original db is the one that should
communicate via ODBC, it is just that some of its tables should be moved
to another separate database/file.

Quote:
> You will have to address this in any install routines of your program.

No install required. There's only a single set of files on our entire
network. Though multiple instances can run at the same time.

Quote:
> Is the 'Program' something you have written?

Nope. Or rather: partially. ColdFusion + my templates (scripts).

Quote:
> If so have you considered accessing the data directly (ADO/DAO etc) instead
> of ODBC?

Our current version of the 'program' can only use ODBC. Period. The next
version just MAY have more capabilities. But I do not (yet) have its
complete spec's and/or full documentation.

--
Greetings from
 _____
 /_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
        Remove SPAMBLOCK from my address ...



Mon, 11 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Auke,
If you split the tables out of your ODBC data source, you will either have
to set up another ODBC data source to access the moved tables via ODBC, or
maybe you can link to the moved tables from the original data source (but I
think that sort of defeats the purpose of splitting).
What exactly are you trying to achieve by splitting the tables up?
And what is trying to communicate with whom? (See comments below)

Jeff


Quote:
> On Wed, 24 May 2000 14:34:06 +1000, "Jeff Davies"

> > Auke,

> Hi Jeff,

> > The ODBC data source can be viewed/set/changed in the control panel-
ODBC
> > applet.

> Yeah, I know. But in this case it is not appropriate.

> > It is neccessary for you to change this, when you split the db, if the
> > original db name is no longer valid.

> Maybe I wasn't clear enough. The original db is the one that should
> communicate via ODBC,

communicate with what?...
Another db?...

Quote:
>it is just that some of its tables should be moved
> to another separate database/file.

...these moved tables?
Why move them then?
Or a third entity?

- Show quoted text -

Quote:

> > You will have to address this in any install routines of your program.

> No install required. There's only a single set of files on our entire
> network. Though multiple instances can run at the same time.

> > Is the 'Program' something you have written?

> Nope. Or rather: partially. ColdFusion + my templates (scripts).

> > If so have you considered accessing the data directly (ADO/DAO etc)
instead
> > of ODBC?

> Our current version of the 'program' can only use ODBC. Period. The next
> version just MAY have more capabilities. But I do not (yet) have its
> complete spec's and/or full documentation.

> --
> Greetings from
>  _____
>  /_|__| Auke Reitsma, Delft, The Netherlands.
> /  | \  -------------------------------------
>         Remove SPAMBLOCK from my address ...



Tue, 12 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
On Fri, 26 May 2000 10:24:44 +1000, "Jeff Davies"

Quote:

> Auke,

Hi Jeff,

Quote:
> If you split the tables out of your ODBC data source, you will either have
> to set up another ODBC data source to access the moved tables via ODBC, or
> maybe you can link to the moved tables from the original data source (but I
> think that sort of defeats the purpose of splitting).
> What exactly are you trying to achieve by splitting the tables up?
> And what is trying to communicate with whom? (See comments below)

Maybe the following diagrammatic simplified explanation may help:

Current:
.  CF( myApp's ) <--> ODBC <--> Database( Tables1, Tables2 )
.  MSWord --> DAO --> Database( Tables2 )

What I want:
.  CF( myApp's ) <--> ODBC <--> Database1( Tables1, Database2( Tables2 ))
.  MSWord --> DAO --> Database2( Tables2 )

In the future Database1 should be hidden from the common user as it will
contain data that should not be made public according to our privacy
rules. Database2 should be accessible in at least a read-only way.  

--
Greetings from
 _____
 /_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
        Remove SPAMBLOCK from my address ...



Wed, 13 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Auke,
The problem I see with that, is you need to change/split the datasource for
CF( myApp's ).
Can you do this?
I would assume there would be a certain level of reprogramming required to
get that app to get it's data from two different sources, but as as I am not
familiar with ColdFusion, I could not be sure.
Maybe you could set up a third db something like;
.   NewDB<-->Linked Tables to <-->Database1( Tables1), Database2( Tables2 )
.  CF( myApp's ) <--> ODBC <--> NewDB
.  MSWord --> DAO --> Database2( Tables2 )
The locations and permissions may stop you here too.

Just a thought,
Jeff Davies


Quote:
> On Fri, 26 May 2000 10:24:44 +1000, "Jeff Davies"

> > Auke,

> Hi Jeff,

> > If you split the tables out of your ODBC data source, you will either
have
> > to set up another ODBC data source to access the moved tables via ODBC,
or
> > maybe you can link to the moved tables from the original data source
(but I
> > think that sort of defeats the purpose of splitting).
> > What exactly are you trying to achieve by splitting the tables up?
> > And what is trying to communicate with whom? (See comments below)

> Maybe the following diagrammatic simplified explanation may help:

> Current:
> .  CF( myApp's ) <--> ODBC <--> Database( Tables1, Tables2 )
> .  MSWord --> DAO --> Database( Tables2 )

> What I want:
> .  CF( myApp's ) <--> ODBC <--> Database1( Tables1, Database2( Tables2 ))
> .  MSWord --> DAO --> Database2( Tables2 )

> In the future Database1 should be hidden from the common user as it will
> contain data that should not be made public according to our privacy
> rules. Database2 should be accessible in at least a read-only way.

> --
> Greetings from
>  _____
>  /_|__| Auke Reitsma, Delft, The Netherlands.
> /  | \  -------------------------------------
>         Remove SPAMBLOCK from my address ...



Fri, 15 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
On Mon, 29 May 2000 09:01:09 +1000, "Jeff Davies"

Quote:

> Auke,
> The problem I see with that, is you need to change/split the datasource for
> CF( myApp's ).
> Can you do this?

That's basically an extended version of what I intended. The extensions
are more or less superfluous. ;-)

Quote:
> I would assume there would be a certain level of reprogramming required to
> get that app to get it's data from two different sources, but as as I am not
> familiar with ColdFusion, I could not be sure.
> Maybe you could set up a third db something like;
> .   NewDB<-->Linked Tables to <-->Database1( Tables1), Database2( Tables2 )
> .  CF( myApp's ) <--> ODBC <--> NewDB

Yes, but the problem is that ODBC does not seem to be able to access the
linked tables in NewDB ...
Question 1 is: is that inherent to ODBC and linked tables?
If not, question 2 is: what stupid mistake did I make?

Quote:
> .  MSWord --> DAO --> Database2( Tables2 )
> The locations and permissions may stop you here too.

> Just a thought,
> Jeff Davies



> > On Fri, 26 May 2000 10:24:44 +1000, "Jeff Davies"

> > > Auke,

> > Hi Jeff,

> > > If you split the tables out of your ODBC data source, you will either
> have
> > > to set up another ODBC data source to access the moved tables via ODBC,
> or
> > > maybe you can link to the moved tables from the original data source
> (but I
> > > think that sort of defeats the purpose of splitting).
> > > What exactly are you trying to achieve by splitting the tables up?
> > > And what is trying to communicate with whom? (See comments below)

> > Maybe the following diagrammatic simplified explanation may help:

> > Current:
> > .  CF( myApp's ) <--> ODBC <--> Database( Tables1, Tables2 )
> > .  MSWord --> DAO --> Database( Tables2 )

> > What I want:
> > .  CF( myApp's ) <--> ODBC <--> Database1( Tables1, Database2( Tables2 ))
> > .  MSWord --> DAO --> Database2( Tables2 )

> > In the future Database1 should be hidden from the common user as it will
> > contain data that should not be made public according to our privacy
> > rules. Database2 should be accessible in at least a read-only way.

> > --
> > Greetings from
> >  _____
> >  /_|__| Auke Reitsma, Delft, The Netherlands.
> > /  | \  -------------------------------------
> >         Remove SPAMBLOCK from my address ...

--
Greetings from
 _____
 /_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
        Remove SPAMBLOCK from my address ...


Sat, 16 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Auke,
"the problem is that ODBC does not seem to be able to access the linked
tables" may be correct.
I just tried to set up an Access db, linked to linked tables in another db,
and couldn't.
I must admit, I have not tried to do that before.

Can you go the other way and have all Tables in db1, db2 has links to the
'Public' tables in db1, use DAO/Word to Access db2. App access db1.
Does that help? (I am starting to lose the plot). Or do you run into the
same probs with DAO and the linked tables in db2?

--
Jeff


Quote:
> On Mon, 29 May 2000 09:01:09 +1000, "Jeff Davies"

> > Auke,
> > The problem I see with that, is you need to change/split the datasource
for
> > CF( myApp's ).
> > Can you do this?

> That's basically an extended version of what I intended. The extensions
> are more or less superfluous. ;-)

> > I would assume there would be a certain level of reprogramming required
to
> > get that app to get it's data from two different sources, but as as I am
not
> > familiar with ColdFusion, I could not be sure.
> > Maybe you could set up a third db something like;
> > .   NewDB<-->Linked Tables to <-->Database1( Tables1),
atabase2( Tables2 )
> > .  CF( myApp's ) <--> ODBC <--> NewDB

> Yes, but the problem is that ODBC does not seem to be able to access the
> linked tables in NewDB ...
> Question 1 is: is that inherent to ODBC and linked tables?
> If not, question 2 is: what stupid mistake did I make?

> > .  MSWord --> DAO --> Database2( Tables2 )
> > The locations and permissions may stop you here too.

> > Just a thought,
> > Jeff Davies



> > > On Fri, 26 May 2000 10:24:44 +1000, "Jeff Davies"

> > > > Auke,

> > > Hi Jeff,

> > > > If you split the tables out of your ODBC data source, you will
either
> > have
> > > > to set up another ODBC data source to access the moved tables via
ODBC,
> > or
> > > > maybe you can link to the moved tables from the original data source
> > (but I
> > > > think that sort of defeats the purpose of splitting).
> > > > What exactly are you trying to achieve by splitting the tables up?
> > > > And what is trying to communicate with whom? (See comments below)

> > > Maybe the following diagrammatic simplified explanation may help:

> > > Current:
> > > .  CF( myApp's ) <--> ODBC <--> Database( Tables1, Tables2 )
> > > .  MSWord --> DAO --> Database( Tables2 )

> > > What I want:
> > > .  CF( myApp's ) <--> ODBC <--> Database1( Tables1,

Database2( Tables2 ))

- Show quoted text -

Quote:
> > > .  MSWord --> DAO --> Database2( Tables2 )

> > > In the future Database1 should be hidden from the common user as it
will
> > > contain data that should not be made public according to our privacy
> > > rules. Database2 should be accessible in at least a read-only way.

> > > --
> > > Greetings from
> > >  _____
> > >  /_|__| Auke Reitsma, Delft, The Netherlands.
> > > /  | \  -------------------------------------
> > >         Remove SPAMBLOCK from my address ...

> --
> Greetings from
>  _____
>  /_|__| Auke Reitsma, Delft, The Netherlands.
> /  | \  -------------------------------------
>         Remove SPAMBLOCK from my address ...



Sun, 17 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Hi Jeff,

On Wed, 31 May 2000 14:21:12 +1000, "Jeff Davies"

Quote:

> Auke,
> "the problem is that ODBC does not seem to be able to access the linked
> tables" may be correct.
> I just tried to set up an Access db, linked to linked tables in another db,
> and couldn't.
> I must admit, I have not tried to do that before.

> Can you go the other way and have all Tables in db1, db2 has links to the
> 'Public' tables in db1, use DAO/Word to Access db2. App access db1.
> Does that help? (I am starting to lose the plot).

That's an alternate way. And I can understand that you are losing the
plot. ;-/  I'm going to put this project on the 'back burner' for a month
or so. Maybe my unconscious will take over with some good ideas. That has
worked before ;-)

Thank you for your interest and suggestions. I appreciate it very much,
but it's time to put an end to this thread.

Quote:
> Or do you run into the  same probs with DAO and the linked tables in db2?

If I remember my past experience with that correctly, there was no problem
at all with DAO. But be warned that my memory is not good ;-)

Quote:
> --
> Jeff



> > On Mon, 29 May 2000 09:01:09 +1000, "Jeff Davies"

> > > Auke,
> > > The problem I see with that, is you need to change/split the datasource
> for
> > > CF( myApp's ).
> > > Can you do this?

> > That's basically an extended version of what I intended. The extensions
> > are more or less superfluous. ;-)

> > > I would assume there would be a certain level of reprogramming required
> to
> > > get that app to get it's data from two different sources, but as as I am
> not
> > > familiar with ColdFusion, I could not be sure.
> > > Maybe you could set up a third db something like;
> > > .   NewDB<-->Linked Tables to <-->Database1( Tables1),
> atabase2( Tables2 )
> > > .  CF( myApp's ) <--> ODBC <--> NewDB

> > Yes, but the problem is that ODBC does not seem to be able to access the
> > linked tables in NewDB ...
> > Question 1 is: is that inherent to ODBC and linked tables?
> > If not, question 2 is: what stupid mistake did I make?

> > > .  MSWord --> DAO --> Database2( Tables2 )
> > > The locations and permissions may stop you here too.

> > > Just a thought,
> > > Jeff Davies

--
Greetings from
 _____
 /_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
        Remove SPAMBLOCK from my address ...


Sun, 17 Nov 2002 03:00:00 GMT  
 ODBC and linked tables
Bye,
See you in a month...
Jeff


Quote:
> Hi Jeff,

> On Wed, 31 May 2000 14:21:12 +1000, "Jeff Davies"

> > Auke,
> > "the problem is that ODBC does not seem to be able to access the linked
> > tables" may be correct.
> > I just tried to set up an Access db, linked to linked tables in another
db,
> > and couldn't.
> > I must admit, I have not tried to do that before.

> > Can you go the other way and have all Tables in db1, db2 has links to
the
> > 'Public' tables in db1, use DAO/Word to Access db2. App access db1.
> > Does that help? (I am starting to lose the plot).

> That's an alternate way. And I can understand that you are losing the
> plot. ;-/  I'm going to put this project on the 'back burner' for a month
> or so. Maybe my unconscious will take over with some good ideas. That has
> worked before ;-)

> Thank you for your interest and suggestions. I appreciate it very much,
> but it's time to put an end to this thread.

> > Or do you run into the  same probs with DAO and the linked tables in
db2?

> If I remember my past experience with that correctly, there was no problem
> at all with DAO. But be warned that my memory is not good ;-)

> > --
> > Jeff



> > > On Mon, 29 May 2000 09:01:09 +1000, "Jeff Davies"

> > > > Auke,
> > > > The problem I see with that, is you need to change/split the
datasource
> > for
> > > > CF( myApp's ).
> > > > Can you do this?

> > > That's basically an extended version of what I intended. The
extensions
> > > are more or less superfluous. ;-)

> > > > I would assume there would be a certain level of reprogramming
required
> > to
> > > > get that app to get it's data from two different sources, but as as
I am
> > not
> > > > familiar with ColdFusion, I could not be sure.
> > > > Maybe you could set up a third db something like;
> > > > .   NewDB<-->Linked Tables to <-->Database1( Tables1),
> > atabase2( Tables2 )
> > > > .  CF( myApp's ) <--> ODBC <--> NewDB

> > > Yes, but the problem is that ODBC does not seem to be able to access
the
> > > linked tables in NewDB ...
> > > Question 1 is: is that inherent to ODBC and linked tables?
> > > If not, question 2 is: what stupid mistake did I make?

> > > > .  MSWord --> DAO --> Database2( Tables2 )
> > > > The locations and permissions may stop you here too.

> > > > Just a thought,
> > > > Jeff Davies

> --
> Greetings from
>  _____
>  /_|__| Auke Reitsma, Delft, The Netherlands.
> /  | \  -------------------------------------
>         Remove SPAMBLOCK from my address ...



Mon, 18 Nov 2002 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. SQL-Server ODBC + Jet Linked Table + RefreshLink method

2. Access and linked ODBC tables don't use ODBC Definition

3. Is it possible to link ActiveData tables to ODBC tables

4. Changing the linked table path without the linked table manager

5. Efficiency of SQL INSERT to ODBC-linked table

6. Updating ODBC linked table via code

7. reconnect linked tables to JET and ODBC

8. No ODBC data sources on link table wizard

9. Programatically Re-linking ODBC tables

10. Linking to Oracle tables from Access97 via ODBC

11. Removing dbo_ prefix from linked ODBC-tables

12. Persist Security Info in Linked ODBC table

 

 
Powered by phpBB® Forum Software