SQL server and Access SELECT in one Query

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
 
C

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
 
C

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
 
R

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.
 
R

Rad [Visual C# MVP]

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Access stripping decimal places in query 1
SQL Server database open in another program 0
Fast SQL Server Stored Procedure runs slow in VB.NET 4
Use DataSet with Sql Server 2005 vs App.Config 1
import excel sheet into a sql server table 7
Am I disturbing a machine in production floor if I access its database remotely? 4
Exec a stor.proc. that includes a query to a linked Access db 1

Top