SQL server and Access SELECT in one Query

[TomH]

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

[Cor Ligthert [MVP]]

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

"TomH" <To**@discussions.microsoft.comschreef in bericht
news:18**********************************@microsof t.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

[Coder]

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


"TomH" <To**@discussions.microsoft.comwrote in message
news:18**********************************@microsof t.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

[RobinS]

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.
-------------------------------------------
"TomH" <To**@discussions.microsoft.comwrote in message
news:18**********************************@microsof t.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

[Michel Posseth [MCP]]

http://www.sqlservercentral.com/colu...kingtables.asp

regards

Michel Posseth

"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

[Rad [Visual C# MVP]]

On Sun, 3 Dec 2006 20:26:00 -0800, 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

I believe in SQL server you can create a linked server to access the access
database
--
Bits.Bytes
http://bytes.thinkersroom.com