Re: How to Mimic Access' Externally Linked Tables using ADO?

From: Brendan Reynolds (brenreyn)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 02:34:56 -0000

I've used linked tables like this when I needed to create queries joining
tables from multiple Jet databases, and it works well in that scenario. But
it sounds as though you're going to be creating queries joining tables from
heterogeneous data sources? You might be joining an ODBC-linked Oracle table
to an ODBC-linked DB2 table? If so, I'm afraid in my experience Jet queries
joining tables from heterogeneous data sources like this tend to be slow. By
all means test the idea to find out whether it might be faster than the
current record-by-record approach, but I wouldn't put too much faith in it
until you've tested it under realistic conditions. 

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"Shawn B" <ext.news.1@bdenim.ca> wrote in message 
news:n2YId.154222$8l.21752@pd7tw1no...
> Thanks for the reply Bill,
>
> My problem is that I have an application that requires access to a number 
> of different DBMS's.  The type and structure of the data coming is not 
> necessarily known ahead of time depending on the source of the data - 
> sometimes it is Oracle, DB2, Text, Excel and who knows what.  The nice 
> thing about ADO, is that it provides a nice means to get at various kinds 
> of data in a relatively uniform way.
>
> The application I need to modify, as is often the case, started out as 
> something small and morphed into this fancy-dandy utility as new 
> requirements arose.  However the problem now is that some of the data we 
> are receiving is large and the application transfers data row by row.  If 
> we could transfer, as Access does, in a query through ADO, things would go 
> much quicker.
>
> I know we can cobble this up using an MS Access "wrapper" around the data 
> and linking to the source and destination DBMSs, but this strikes me as 
> inelegant if there is a corresponding means within ADO.  By your response, 
> I imagine I am destined to go my "inelegant" route.
>
> Thanks again,
>
> Shawn.
>
>
>
> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message 
> news:eOxh6kCAFHA.1264@TK2MSFTNGP12.phx.gbl...
>> ADO does  not have a query engine. It's an interface to query engines. 
>> It's the query engine that does all the database IO, linking and joining. 
>> What DBMS are you accessing? Many have similar "linked" table features.
>>
>> -- 
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no 
>> rights.
>> __________________________________
>>
>> "Shawn B" <ext.news.1@bdenim.ca> wrote in message 
>> news:41f1765a$1@obsidian.gov.bc.ca...
>>> Hi All,
>>>
>>> I would like to simulate/mimic the behaviour of Microsoft Access' 
>>> "Externally Linked Tables" from within ADO so that I can, in effect, 
>>> perform a single query based on tables from multiple 
>>> databases/connections.  Is this possible?  If so, how?
>>>
>>> Thanks in Advance,
>>>
>>> Shawn.
>>>
>>
>>
>
>