Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 824169 - Last Review: March 28, 2007 - Revision: 3.1
Results do not appear when you run a query in an Access database and the query references linked SQL Server tables
This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
Moderate: Requires basic macro, coding, and interoperability
skills.
SYMPTOMS
When you run a query in a Microsoft Access database that
joins linked Microsoft SQL Server tables from different sources or databases,
the query may not return any data.
However, when you run a similar
query that refers to the linked SQL Server tables from the same source or
database, this problem does not occur.
WORKAROUND
To work around this problem, use one of the following
methods:
Rewrite the Query
You can rewrite the Microsoft Access query to use the join
criteria with the LIKE keyword. You can use the LIKE operator to compare one
field to the other field in the linked tables from different databases instead
of using ANSI JOIN.
For example, if the original query is the
following, where dbo_db1_table1 is linked from the SQL Server database DB1 and
dbo_db2_table1, dbo_db2_table2 are linked from the SQL Server database DB2:SELECT
dbo_db1_table1.db1_table1_col1,
dbo_db2_table1.db2_table1_col1,
dbo_db2_table2.db2_table2_col1
FROM
(
dbo_db1_table1 INNER JOIN dbo_db2_table1
ON
dbo_db1_table1.db1_table1_col2 = dbo_db2_table1.db2_table1_col2
) INNER JOIN dbo_db2_table2
ON
dbo_db2_table1.db2_table1_col3 = dbo_db2_table2.db2_table2_col3;
SELECT
dbo_db1_table1.db1_table1_col1,
dbo_db2_table1.db2_table1_col1,
dbo_db2_table2.db2_table2_col1
FROM
dbo_db1_table1,
dbo_db2_table1 INNER JOIN dbo_db2_table2
ON
dbo_db2_table1.db2_table1_col3 = dbo_db2_table2.db2_table2_col3;
WHERE
(((dbo_db1_table1.db1_table1_col2 LIKE dbo_db2_table1.db2_table1_col2));
Use a Microsoft Access Database Project
To avoid the problem that is mentioned in the "Symptoms" section
of this article, use a Microsoft Access database project instead of a Microsoft
Access database. Link the appropriate SQL Server tables in the Microsoft Access
database project, and then create a stored procedure to include the following
query (where dbo_db1_table1 is linked from the SQL Server database DB1 and
dbo_db2_table1, dbo_db2_table2 are linked from the SQL Server database DB2):SELECT
dbo_db1_table1.db1_table1_col1,
dbo_db2_table1.db2_table1_col1,
dbo_db2_table2.db2_table2_col1
FROM
(
dbo_db1_table1 INNER JOIN dbo_db2_table1
ON
dbo_db1_table1.db1_table1_col2 = dbo_db2_table1.db2_table1_col2
) INNER JOIN dbo_db2_table2
ON
dbo_db2_table1.db2_table1_col3 = dbo_db2_table2.db2_table2_col3;
Import the SQL Server Tables
To avoid the problem that is mentioned in the "Symptoms" section
of this article, import the SQL Server tables to the Microsoft Access database
instead of linking the SQL Server tables to the Access database.
Note If you import the SQL Server tables to the Access database, you
cannot use the latest data in the imported table because the imported table
contains the snapshot of the data at the time you import the table.
MORE INFORMATION
You can filter the data in Microsoft Access by using the
JOIN keyword in the queries. The JOIN keyword is classified as ANSI JOIN and
NON ANSI JOIN. ANSI JOIN uses JOIN and ON keywords in the query. NON ANSI JOIN
uses a WHERE clause in the query.
NON ANSI JOIN was used more
frequently before the evolution of ANSI 92 SQL and was upsized to ANSI JOIN.
Microsoft recommends that you use ANSI JOIN in your queries to filter the
data.
REFERENCES
For more information about how to run the
Upsizing Wizard, click
Microsoft Office Access Help on the
Help menu, type
Upsizing Wizard in the
Search for box in the Assistance pane, and then click
Start searching to view the topic.
For additional information, click the following article
number to view the article in the Microsoft Knowledge Base:
287414Â
(http://kbalertz.com/Feedback.aspx?kbNumber=287414/
)
You Cannot Link Tables into a Microsoft Access Project by Using an ODBC Data Source Name (DSN)
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
- Microsoft Access 2000 Standard Edition
| kbquery kbimport kbprb KB824169 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please