Issue with Pass through Query and ODBC connection

[pradeep.atta]

Hi,

My front end access tables are linked with backed SQL server 2008 tables

Access table names:
TWF MERGE 1
TWF MERGE 2

SQL server table names:
TWF_MERGE_1
TWF_MERGE_2

I have linked the tables using connection strings and tables are linked successfully.

I am getting error message as "Invalid Object Name TWF MERGE 0 #208" when i tried to run the below query from MS ACCESS:

SELECT count(*) FROM [TWF MERGE 0]

But it is working fine when I run the below query from ACCESS by changing the table name to SQL one:

SELECT count(*) FROM [TWF_MERGE_0]

What I need to do to run the query with Access table name with out any issues? There are hundereds of queries which point to acces table names and need to run them without changing them to SQL ones.

 

[tranchemontaigne]

This sounds a lot like an issue I've got in the VBA forum. When I copy an Oracle table into a local MS Access table everything works normal. When I connect to the linked Oracle table things fail. The going hypothesis is that the MS Query Optimizer is finding fields with indexes and optimizing the query for MS Jet to the point where it is no longer acceptible to the backend database engine. Pass through queries are the solution to this predicament, though I'm having trouble defining the connection string.

One approach (that has not worked for me) is to run TOOLS ==> ANALYZE ==> DOCUMENTER to view the connection string behind the linked table reference.

I hope this adds some value.

 

[mdlueck]

SELECT count(*) FROM [TWF MERGE 0]

vs

SELECT count(*) FROM [TWF_MERGE_0]

You are slamming into the same subtle differences I found when learning how to deal with Access in a Clinet/Server environment.

Shortly, the SQL you generate is specific to the DB which will process it / it is pointed at.

I would highly suggest when you are developing queries targeting SQL Server to push the Pass-Through button for those queries. In that mode, Access sends the SQL BE DB the SQL as-is and does not "optimization" of the SQL. Access and SQL RDBMS's have different SQL syntax, thus Access can not understand the SQL preferences of any SQL back end.

Here is a post of mine describing briefly how I am using Access in a Client/Server environment.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Such is the area you are getting into.