1

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?

Improve this question
1
  • 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. Commented Oct 19, 2009 at 11:14

4 Answers 4

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Can you explain exactly when you believe that approach is useful? For the vast majority of cases, linked tables are just easier to manage. I think it's really problematic to bury connect strings in SQL strings, whether stored as saved QueryDefs, as Recordsources/Rowsources in forms/reports, and in VBA code. It's just cleaner to have the linked table as an object that can be used as though it exists in your Access database.
I cannot. You are probably correct: I think it's really problematic to bury SQL code in strings, whether stored as Recordsources/Rowsources in forms/reports, and in VBA code; it's cleaner (among other reasons e.g. code maintenance) to have the SQL code as an object (VIEW or PROCEDURE) that can be used throughout your application and any other application that will use your database (which will happen if the database is successful in the enterprise). Please edit to add your reasons for preferring a linked table :)
Where do I run this kind of query on ?
1

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.

Improve this answer

Comments

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.

Improve this answer

Comments

0

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

Improve this answer

1 Comment

sorry, I missed that part saying you want the query to take place in ms access. In that case use linked table as suggested in other answers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.