SQL server and Access SELECT in one Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
 
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
Click to expand...

I believe in SQL server you can create a linked server to access the access
database
 
Back
Top