|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL server and Access SELECT in one QueryHow do I create one SELECT query in VB .NET that pulls from a SQL Server
database and a MS Access database? The SQL server table has a unique identifier as a PK and the Access table has a autonumber (replication ID) as a FK, which matches the SQL table PK, so I know I can join them on this field. Currently, I am using SQLCommand to access only the SQL server data and DAO to access only the MS Access data. Is there a way to create a SQLConnectionString that points to multiple DBs? What is the best approach? Thanks, Tom Tomh,
If you ask this in the newsgroup microsoft.public.dotnet.framework.adonet Than you have much more change on an answer on this particular question. Cor Show quoteHide quote "TomH" <T***@discussions.microsoft.com> schreef in bericht news:18D0CD10-1B71-436B-A796-43A6928E7491@microsoft.com... > How do I create one SELECT query in VB .NET that pulls from a SQL Server > database and a MS Access database? The SQL server table has a unique > identifier as a PK and the Access table has a autonumber (replication ID) > as > a FK, which matches the SQL table PK, so I know I can join them on this > field. > > Currently, I am using SQLCommand to access only the SQL server data and > DAO > to access only the MS Access data. Is there a way to create a > SQLConnectionString that points to multiple DBs? > > What is the best approach? > > Thanks, > > Tom Why use DAO? It's so old I can't even remember the syntax any more...
Maybe your question hides specific details that mean you are limited to existing code or something... if not use ADO.Net and save a lot of hassle. You could fill one table in a dataset with a sqldataadapter, and another with the oledbadapter (for Access), and also define your relationship in the dataset between the two tables, exactly as you described plus with the options of referential integrity checking, cascaded deletes, updates etc etc. That's what it's for isn't it? Cheers Show quoteHide quote "TomH" <T***@discussions.microsoft.com> wrote in message news:18D0CD10-1B71-436B-A796-43A6928E7491@microsoft.com... > How do I create one SELECT query in VB .NET that pulls from a SQL Server > database and a MS Access database? The SQL server table has a unique > identifier as a PK and the Access table has a autonumber (replication ID) > as > a FK, which matches the SQL table PK, so I know I can join them on this > field. > > Currently, I am using SQLCommand to access only the SQL server data and > DAO > to access only the MS Access data. Is there a way to create a > SQLConnectionString that points to multiple DBs? > > What is the best approach? > > Thanks, > > Tom I don't know of any way that you can open two databases with the
same connection. Why do you want to? Is there another solution to resolve what you're trying to accomplish? The only thing I can think of is you could add linked tables to your Access database that access the SQLServer database tables you're interested in, and then you could query those tables and your pure Access tables together. Robin S. ------------------------------------------- Show quoteHide quote "TomH" <T***@discussions.microsoft.com> wrote in message news:18D0CD10-1B71-436B-A796-43A6928E7491@microsoft.com... > How do I create one SELECT query in VB .NET that pulls from a SQL Server > database and a MS Access database? The SQL server table has a unique > identifier as a PK and the Access table has a autonumber (replication ID) > as > a FK, which matches the SQL table PK, so I know I can join them on this > field. > > Currently, I am using SQLCommand to access only the SQL server data and > DAO > to access only the MS Access data. Is there a way to create a > SQLConnectionString that points to multiple DBs? > > What is the best approach? > > Thanks, > > Tom
http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlserverlinkingtables.asp
regards Michel Posseth Show quoteHide quote "TomH" wrote: > How do I create one SELECT query in VB .NET that pulls from a SQL Server > database and a MS Access database? The SQL server table has a unique > identifier as a PK and the Access table has a autonumber (replication ID) as > a FK, which matches the SQL table PK, so I know I can join them on this field. > > Currently, I am using SQLCommand to access only the SQL server data and DAO > to access only the MS Access data. Is there a way to create a > SQLConnectionString that points to multiple DBs? > > What is the best approach? > > Thanks, > > Tom On Sun, 3 Dec 2006 20:26:00 -0800, TomH wrote:
Show quoteHide quote > How do I create one SELECT query in VB .NET that pulls from a SQL Server I believe in SQL server you can create a linked server to access the access> database and a MS Access database? The SQL server table has a unique > identifier as a PK and the Access table has a autonumber (replication ID) as > a FK, which matches the SQL table PK, so I know I can join them on this field. > > Currently, I am using SQLCommand to access only the SQL server data and DAO > to access only the MS Access data. Is there a way to create a > SQLConnectionString that points to multiple DBs? > > What is the best approach? > > Thanks, > > Tom database |
|||||||||||||||||||||||
Other interesting topics