Using ODBC Scalar Functions via the Microsoft.ACE.OLEDB.12.0 provider raises error -2147217900: Malformed GUID in query expression

The error -2147217900: Malformed GUID in query expression when using ODBC Scalar Functions via the Microsoft.ACE.OLEDB.12.0 provider may stem from the way the ACE provider interprets the syntax for scalar functions. While Microsoft Access SQL supports the use of ODBC defined syntax for scalar functions, compatibility issues can arise when using the ACE OLEDB provider, particularly with certain functions or when the syntax is not fully supported.

In your case, the expression {fn CONVERT(ID, SQL_INTEGER)} might not be correctly interpreted by the ACE provider, leading to the GUID error. It is important to ensure that the syntax used is compatible with the OLEDB provider. You might consider alternative approaches, such as using standard Access SQL functions or ensuring that the data types in your expressions are explicitly defined in a way that the ACE provider can process correctly.

If the issue persists, checking for updates or patches for the ACE provider might also help, as improvements and bug fixes are regularly released that could address such compatibility issues.

References:

• Unable to use the Access ODBC, OLEDB, or DAO interfaces outside Office Click-to-Run applications
• Escape Sequences in ODBC

Hello Brett Miles

Thank you for posting your question on the Q&A forum.

I really appreciate your engagement and the clarity of your explanation. It’s completely understandable how frustrating it can be when expected syntax like ODBC scalar functions doesn’t behave as documented, especially when working with the ACE OLEDB provider.

If you replace the original query with:

SELECT * FROM Location t1
WHERE CInt(t1.ID) > 20;

does it work as expected?

If it does, you might consider alternative approaches, such as using standard Access SQL functions or ensuring that the data types in your expressions are explicitly defined in a way that the ACE provider can process correctly.

If the issue persists, and if you're comfortable with it, feel free to share the relevant portion of your code and a sample of the data for better assistance. You can just add a comment here, and I’ll follow up via private message to ensure the data is transferred securely and confidentially.

Looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.