Solved

The column prefix 'MSx' does not match with a table name or alias name used in the query. (#107)

Posted on 2010-11-18
3
996 Views
Last Modified: 2012-05-10
Hi there, we are using an Access 2007 database with linked tables to a SQL Server 2000 database.

We have 3 queries set up as follows:

SELECT dbo_Division.divname, dbo_Department.deptname, dbo_Decision.decname, dbo_object.objectname, dbo_MonthBalance.acctcode, dbo_MonthBalance.monthdate, dbo_MonthBalance.monthactual
FROM ((((dbo_Division INNER JOIN dbo_Department ON dbo_Division.divcode = dbo_Department.divcode) INNER JOIN dbo_Decision ON dbo_Department.deptcode = dbo_Decision.deptcode) INNER JOIN dbo_Account ON dbo_Decision.deccode = dbo_Account.deccode) LEFT JOIN dbo_MonthBalance ON dbo_Account.acctcode = dbo_MonthBalance.acctcode) INNER JOIN dbo_object ON dbo_Account.objectcode = dbo_object.objectcode
WHERE (((dbo_MonthBalance.monthdate)="200803") AND ((dbo_Account.category)="5"));

note: query #1 is monthdate=200803, query #2 is monthdate=200903 and query #3 is monthdate=201003.

This is to compare data over 3 years.

And then we have a number of identical queries for each department based on the data from these 3 queries, for example:

SELECT [201003balances].decname, [201003balances].objectname, [201003balances].acctcode, [200803balances].monthactual, [200903balances].monthactual, [201003balances].monthactual
FROM (200803balances RIGHT JOIN 200903balances ON [200803balances].acctcode = [200903balances].acctcode) RIGHT JOIN 201003balances ON [200903balances].acctcode = [201003balances].acctcode
WHERE ((([201003balances].deptname)="Marketing"))
ORDER BY [201003balances].divname, [201003balances].deptname;

These queries are resulting in the following error in Access 2007:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS2' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS2' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS2' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS3' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS2' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS4' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS3' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS2' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS5' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS6' does not match with a table name or alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MS4' does not match with a table name or alias name used in the query. (#107)

The query works fine if copied in to SQL Query Analyzer, however not all of our staff have direct access to the SQL database, which is why they are using ODBC in Access 2007.

All computers are running MS Windows XP with SP3.  I've verified that we have the most up-to-date version of the JET database.

Any solutions would be greatly appreciated.

Thanks!
0
Comment
Question by:book-worm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 1

Author Comment

by:book-worm
ID: 34167787
Update:  I've just discovered that the query runs fine in Access 2007 SP1 and earlier, as one staff member was able to run the queries without any errors.

MS Access 2007 12.0.4518.1014        MSO 12.0.6320.5000 - works
MS Access 2007 12.0.6211.1000 SP1 MSO 12.0.6213.1000 - works
MS Access 2007 12.0.6423.1000 SP2 MSO 12.0.6529.5000 - doesn't work

Is this likely a bug introduced in SP2 then?


0
 
LVL 17

Accepted Solution

by:
aflockhart earned 500 total points
ID: 34171124
Looks like a possible explanation. If it's linked tables, the problem

If you have access to the SQL Server, you could use the profiler tool to see what is actually being submitted to the SQL Server database.  

If you haven't already solved this you could also think about changing the logic so that you define and run the query on the SQL Server, in a stored procedure to which you can pass parameters,  then create "passthrough" queries in Access to call the stored procedure.
0
 
LVL 1

Author Comment

by:book-worm
ID: 34191292
Thanks aflockhart, I ran the profiler tool and the query being sent from Access (SP2) is being submitted with "MS1", "MS2", etc.

Unfortunately the end users do not have access to the SQL Server and I'm not authorized to created stored procedures.  Our development team is likely too busy to create stored procedures whenever end users need them, so that solution won't work.

Are there better ways the above queries could be written to avoid the issue we're having?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question