Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
1,009 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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.

721 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