Results do not appear when you run a query that references Linked SQL Server tables (824169)

WORKAROUND

• 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;
  

  rephrase the query as the following:

  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));

  Note Here, both ANSI JOIN and NON ANSI JOIN syntax appear in the same query.

• 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;
  

  The result set will appear when you run this stored procedure.

• 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.