[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1660
  • Last Modified:

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
0
BKBroiler
Asked:
BKBroiler
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now