EOleException (Unspecified Error) when querying an Access table from Delphi using ADO

Setup : Delphi 5 Enterprise with Update Pack 1
Application : Email Address Database using ADO to connect to SQL Server 2000 Tables and MS Access table (2003 version) with Jet 4.0 for offline use

I have an application to keep track of over 800 email addresses.  I have reports and filtering setup using ADOQueries and it works perfectly in SQL 2000.  It also runs very smoothly using the offline Access tables, except when you try to filter query results on one field.

I have a field named 'Domain' which stores the email domain information.  There are only 6 possible values, which would be something like 'mail.com', 'messages.biz', etc.  I also have a value called '<None>' that tracks removed addresses.  When I filter data in my filter form, I do not want removed addresses to be shown, so I always tag on a (AND Domain <> '<None>') to my filter string to prevent showing those addresses.

My entire string would look like : select * from EMAIL where (......) AND Domain <> '<None>'.

So, this has always worked in SQL, but for Access, if I do anything with this one Domain field, I get an EOleException (Unspecified error) when opening the query.  If  I make any combination of filters using any of the other 14 fields, the queries work, it's only when I add the Domain field to the string do I get the error.  In the database, the field is just a 15 character standard text field and is not a key field.  There's nothing special about it.

Any help with this one would be appreciated.

Todd
LVL 1
BKBroilerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
RickJCommented:
I am not 100% sure, but I think DOMAIN is a reserved word.
Does it work if you specify your query as follows.

select * from EMAIL E where (......) AND E.Domain <> '<None>'.

0
 
RickJCommented:
Or this
select * from EMAIL E where (......) AND E."Domain" <> '<None>'.

Sorry I am not sure of the exact syntax in access.
This is the way reserved words are handled in my database.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
BKBroilerAuthor Commented:
Thanks RickJ.  Your answer wasn't the correct fix, but it did lead me to it.  I did check to see if 'Domain' was a reserved word in Delphi before submitting the original, but did not think to check for Access.  It is not reserved, according to the following article:

http://support.microsoft.com/kb/286335/

However, for some reason, I believe it does hold a special meaning somewhere, even though I can't find it written.  When I changed the field name to 'EmailDomain', everything seems to be working.  Now it's just a matter of redefining field components, grids, etc....  The tedious stuff........

By the way, I did try your suggestions first, but couldn't get the syntax to work in several different variations.

Thanks for the quick reply though!

Todd
0
 
RickJCommented:
It is listed in this document for Jet. http://support.microsoft.com/?kbid=248738
I think the correct syntax is as follows

select * from EMAIL where (......) AND [Domain] <> '<None>'.

0
 
BKBroilerAuthor Commented:
You are correct, sir!  Yet another one that I didn't think to check out.  My resolution was to rename the field to 'EmailDomain' in SQL and Access and now things are running smoothly again.

Thanks again for your help and for posting that new KB article as well.

Todd
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.