CODEWITHSUNDEEP
sql-serverms-access
Gopal
Gopal

Reputation: 11982

How to make a select query for sql and access databases?

Using SQL server 2000 and Access 2003

Access Database Name - History.mdb 
Access Table Name - Events

SQL Database Name - Star.mdf
SQL Table Name - Person

I want to take the field from person table, and include in Events table by using inner join

Tried Query

Select * from Events inner join person where events.id = person.id

So How to make a query for access and sql databases.

I want to make a Select query in access only. Not an sql Database.

Need Query Help?

Upvotes: 1

Views: 397

Answers (4)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

You can set up a linked table in Access to your SQL Server, and the instructions on how to do so vary slightly in Access versions. Look in the help file for "Linked Table", or go here if you have Access 2007.

Once you have a linked table set up, you'll be able to access the SQL Server table in your query. Note that optimizing a linked table join takes some work.

Upvotes: 1

user114600
user114600

Reputation:

You can create a linked table in Access, that points to the table in SQL. The rest you can achieve in the query designer.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

While you can (possible, should -- why?) use a linked table, there are as ever more than one way to skin a cat. Here's another approach: put the connection details into the query test e.g. something like

SELECT * 
  FROM [ODBC;Driver={SQL Server};SERVER=MyServer;DATABASE=Star;UID=MyUsername;Pwd=MyPassword;].Person AS P1 
       INNER JOIN 
       [MS Access;DATABASE=C:\History;].[Events] AS E1
          ON S1.seq = S2.seq
 WHERE E1.id = P1.id;

Upvotes: 3

Tzury Bar Yochay
Tzury Bar Yochay

Reputation: 9004

You should add the msaccess db as a remote server. then you can do that join

Upvotes: 0

Related Questions