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

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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;