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
989 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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