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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 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