Column prefix dbo.x does not match with a table... Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have an Access 97 mdb. It has several linked tables to real tables in a
MS SQL Server 7. It worked fine up until a month ago. Now after we refresh
its linked tables we are getting the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix
'dbo.a_qkit' does not match a table name or alias name used in the query.
(#107)

If we take the old mdb and execute the query it works just fine.
If we take the new mdb it reports this error. All this with the same
machine, same database, same database server, etc. The only change we are
making it is just refreshing the links. The tables' structure has not
changed since they were linked for the last time.


The query is a multi-join with two left joins as follows:

SELECT qmatejec.idmatejec, qmatejec.idejec, qmatejec.idmaterial,
articulos.nombre, qmatejec.unidades, qmaterial.reutilizable,
qkitejec.idkitejec, qkitejec.idkit, qmatejec.enlaceifactur, qkit.descripcion
FROM ((articulos INNER JOIN (qmatejec INNER JOIN qmaterial ON
qmatejec.idmaterial = qmaterial.codigo) ON (articulos.familia =
qmaterial.familia) AND (articulos.grupo = qmaterial.grupo) AND
(articulos.subgrupo = qmaterial.subgrupo)) LEFT JOIN qkitejec ON
qmatejec.idkitejec = qkitejec.idkitejec) LEFT JOIN qkit ON qkitejec.idkit =
qkit.codigo where qmatejec.idejec=149 ORDER BY
qkitejec.idkitejec,qmaterial.descripcion

We did an ODBC trace and this is what's happening:
WHEN IT WORKS:
Access makes a select of the table a_qkit (which is the real name for the
qkit alias used in the query) and then executes a select statement with no
reference to this table, making what we guess is a local-join.
WHEN IT DOESN'T WORK
Access sends a query to the server that it does not have the "a_qkit" table
in the from clause but it is being used as a condition for the join. It's
splitting the select incorrectly, leaving part of the statemet that should
not be there!

Any clues as what may be happening?
Does anyone know if refreshing links does update the info Access has about
the table's data distribution or number of rows, that will make the jet
engine change its mind about the optimal way to execute queries? Is this
info kept in the mdb a all?
 
Hi,

From your descriptions, I understood that you encounter the error
ODBC--Call Failed."The column prefix 'name' does not match with a table
name or alias name used in the query. (#107) " Have I understood you?
Correct me if I was wrong.

Based on my scope, it seems the following knowledge base articles confirmed
it to be an known issue of us

ACC2000: "ODBC Call Failed" Error When You Run a Query with an Outer Join
Against Linked SQL Server Tables
http://support.microsoft.com/kb/300830

You could download the latest Jet 4.0 Database Engine Services Pack 8 to
resolve this issue. Select the download version according to your operating
systsem in the following links

How to obtain the latest service pack for the Microsoft Jet 4.0 Database
Engine
http://support.microsoft.com/kb/239114

Hope this helps and don't hesitate to let me know if you have any questions
or concerns. We are always here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
Hi Michael,
From your descriptions, I understood that you encounter the error
ODBC--Call Failed."The column prefix 'name' does not match with a table
name or alias name used in the query. (#107) " Have I understood you?
Correct me if I was wrong.
Click to expand...

That is the error we are experiencing.
You could download the latest Jet 4.0 Database Engine Services Pack 8 to
resolve this issue. Select the download version according to your operating
systsem in the following links
Click to expand...

We are using Jet 3.51, since we are accessing an Access 97 database.
Is there a patch for 3.51?

Could you PLEASE give us a little insight as to why this is happening now,
and it did not happen a month ago (no changes were made on the database or
the query)? Any pointer (online article, thread) about the type of data
that links in an Access database keep from the real table (besides what's
visible: columns, indexes, connection strings...)???? If we open an Access
database with notepad we can see references to tables we have not linked, so
it definately stores more info than what's visible. They are even tables
that have nothing to do with the linked tables. Does Access use this
additional info to optimize the queries? What are the factors that Access
takes into account when it splits SELECT's that are being sent to a server?

We have many installations with this setup and only 1 client is having this
problem. The client is running Windows NT 4 and Windows 95.

Regards,

Antonio Iglesias


"Michael Cheng [MSFT]" said:
Hi,


Based on my scope, it seems the following knowledge base articles confirmed
it to be an known issue of us

ACC2000: "ODBC Call Failed" Error When You Run a Query with an Outer Join
Against Linked SQL Server Tables
http://support.microsoft.com/kb/300830


How to obtain the latest service pack for the Microsoft Jet 4.0 Database
Engine
http://support.microsoft.com/kb/239114

Hope this helps and don't hesitate to let me know if you have any questions
or concerns. We are always here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Click to expand...
 
HI Antonio,

It was caused by a known issue in Jet 3.5x and was fixed in Jet 4.0 Service
Pack 5. I am afraid it won't be fixed in Jet 3.5x, however, I am not sure
why refreshing link will raise it.

For now, if you take out the LEFT OUT in the FROM statement, will it works
smoothly as expected? If so, we would like to suggest breaking out the left
join logic in a separate query from access, or consider writing a pass thru
query to accomplish the same thing, or possibly write the query as a view
on SQL.

If this has much business impact for you, looking at the nature of this
issue, it would require intensive troubleshooting which would be done
quickly and effectively with direct assistance from a Microsoft Support
Professional through Microsoft Product Support Services. You can contact
Microsoft Product Support directly to discuss additional support options
you may have available, by contacting us at 1-(800)936-5800 or by choosing
one of the options listed at
http://support.microsoft.com/directory/overview.asp

Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
Hi,

I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!

Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
Hi,

We did install the service pack and it did work. But this means changing all
our applications to jet 4.
Do you know if jet 4 has some incompatibility with Access 97 format. Is it
possible to create tables with jet 4 on an Access 97 database? I remember
having read some article that said it is not possible. Any other issues?
Do you have a pointer to info about this?

Regards,

Antonio.
 
Hi Antonio,

It was Access 97 format, but you use Access 2000 or higher to open it? If
so, Access 2000 will use Jet 4 to open the Accdess 97 format. If not, would
you please let me know how could you upgrade this to Jet 4.0?

Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
Back
Top