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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how the fundamental information of how to create a table.

713 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