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

From: Shawn B (ext.news.1_at_bdenim.ca)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 00:09:24 GMT

Thanks again,

If I were to go the MS Access Wrapper route, is it possible to define the
linkage to the external DBMS using ADO or ADOX or some other library (Jet?)?

Bill Vaughn in his parallel reply also suggests using the same strategy
using SQL Server for efficiency reasons. The problem with SQL Server is
that we do not have an accessible instance of SQL Server, however so this
route is slightly more problematic. Same questions though: can I set the
connection up programmatically using ADO or ADOX or some SQL Server API?

The hope here is that I can place the connect information into my
application's configuration and let the application set up the connection
through my MS Access "wrapper". This would make the connection
configuration consistent (wow, lots of co* words) with how "normal"
connections are made.

Your other comment on heterogenous JOINs is true. I will in effect be doing
such a thing. If it turns out to be inefficient though, I would likely
perform an INSERT INTO statement into a more efficient data store before
doing the JOIN. So if nothing else works, I need only an efficient SELECT *
FROM.

Thanks,

Shawn.

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:%23ydGM1bAFHA.2076@TK2MSFTNGP15.phx.gbl...
> 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.
>>>>
>>>
>>>
>>
>>
>
>

Relevant Pages

  • Re: Sql Server 2000 slow
    ... If I close the connection (moving the Open/Close ... ADO) and the dll gives twice the performance. ... > Are there any known performance issues with .NET accessing Sql Server ...
    (microsoft.public.dotnet.framework.performance)
  • Re: Cross Transactions between ADO & ADO.Net
    ... MARS session pool limited to one connection?). ... > There is one thing that might help here, if you're using SQL Server. ... We even call ADO code from ... >> the DTC cordinator from COM+ to handle the transactions between ADO.Net ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: absolute newbie question - connecting to SQL server in ADO.net
    ... How do I get the above connection working? ... > experience of ADO prior to this was in Access 2000. ... Have I wasted my money buying VB.net standard because it can't connect to ... > an SQL server 2000 server? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection issues with ADO
    ... >With ADO 2.5 the default connection protocol was named pipes. ... I can always use Named Pipes to connect to my local sql server. ...
    (microsoft.public.vb.general.discussion)
  • Re: Error -2147168227 Cannot create new transaction because capacity was exceeded.
    ... I'm not using nested transactions. ... I've got a wrapper class on the ADO connection ... Prior to that I've just closed the ADO recordset object. ... >> I have been unable to find info in the Knowledge base, ADO or SQL Server ...
    (microsoft.public.data.ado)