Post reply

Multi-part identifier could not be bound with Access generated SQL

  • October 8, 2007 at 6:27 pm

    #178812

    I am using Access to query a linked SQL Server 2005 database. The queries were all developed for an earlier Access database (subsequewntly migrated to SQL Server), and worked fine there.

    One of our queries is generating errors of the form...

    [Microsoft][ODBC SQL Server Driver][SQL Server] The multi-part identifier "MS2.keyStatusType" could not be bound. (#4104)

    The Access generated SQL behind the query that is failing is...

    SELECT vtblProduct.keyProduct, vtblProduct.keyProductType_, vtblProduct.txtProductCode_, vtblProduct.txtSerialNumber, vtblAgreement2Product.keyAgreement_

    FROM vtblProduct

    LEFT JOIN vtblAgreement2Product ON vtblProduct.keyProduct = vtblAgreement2Product.keyProduct_

    WHERE (((vtblAgreement2Product.keyAgreement_) Is Null));

    The keyStatusType column (and others) referred to in the error, is a column from the vtblAgreement2Product subquery referenced in the SQL above. This subquery is running fine in isolation, and its SQL (also Access generated) is...

    SELECT tblAgreement2Product.*

    FROM tblAgreement2Product INNER JOIN (tblStatusType INNER JOIN tblStatus ON tblStatusType.keyStatusType = tblStatus.keyStatusType_) ON tblAgreement2Product.keyAgreement2Product = tblStatus.keyField_

    WHERE (((tblStatus.keyTableName_)=7) AND ((tblStatus.dtEnd)>=Now()) AND ((tblStatus.dtStart_)<=Now()) AND ((tblStatusType.txtDescription_)='Active')) OR (((tblStatus.keyTableName_)=7) AND ((tblStatus.dtEnd) Is Null) AND ((tblStatus.dtStart_)<=Now()) AND ((tblStatusType.txtDescription_)='Active'));

    I believe every column referred to in this subquery has generated an equivalent error line as the example above.

    This seems to be related to the outer join in some way. The query works fine with an inner join (other than it doesn't do what I need!). I have no access to the table definitions or structures in the database, but full control over what the Access side does.

    I'm assuming that the SQL Access is generating is in some way incompatible with SQL Server. I've found some comments in here with similar errors when table alias's are not being used consistently, but none of this SQL uses alias at all. Any ideas?

    Thanks and regards

    Andrew

  • October 12, 2007 at 4:16 am

    #741492

    I've sorted out a workaround, but not all all certain why it works.

    The left side of the outer join was itself a nested query, and flattening this query hierarchy has let it work.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply