Re: How to Mimic Access' Externally Linked Tables using ADO?
From: Brendan Reynolds (brenreyn)
Date: 01/25/05
- Next message: steven scaife: "copying recordset from one database into a copy of the database"
- Previous message: Anders Eriksson: "Re: Index and Deleted"
- In reply to: Shawn B: "Re: How to Mimic Access' Externally Linked Tables using ADO?"
- Next in thread: William \(Bill\) Vaughn: "Re: How to Mimic Access' Externally Linked Tables using ADO?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 Jan 2005 10:21:05 -0000
There's an example of how to programmatically relink ODBC-linked tables at
the URL below that you may be able to adapt. It uses DAO, but I expect it
could be modified to use ADO/ADOX if necessary. I can't comment on SQL
Server linked tables as I have no experience in that area.
http://www.mvps.org/access/tables/tbl0010.htm
-- 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:U6gJd.161527$6l.143792@pd7tw2no... > 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. >>>>> >>>> >>>> >>> >>> >> >> > >
- Next message: steven scaife: "copying recordset from one database into a copy of the database"
- Previous message: Anders Eriksson: "Re: Index and Deleted"
- In reply to: Shawn B: "Re: How to Mimic Access' Externally Linked Tables using ADO?"
- Next in thread: William \(Bill\) Vaughn: "Re: How to Mimic Access' Externally Linked Tables using ADO?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
