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

1
votes

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?

4
sql-serverms-access
Gropal, I agree with comments below re creation of a table in ms-access that links to the SQL server table. If the Person table is large then make sure you have an index on person.id else ms-access will perform a full table scan across the network every time the query runs. - heferav

4 Answers

3
votes

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;
1
votes

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.

1
votes

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

0
votes

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