Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Automation Error, Unspecified Error when opening ado recordset

Posted on 2007-11-19
7
Medium Priority
?
2,270 Views
Last Modified: 2010-05-18
I'm running a query and get the error in the subject line above.  This
is the query:

strSQL = "SELECT XL_Import.Gate, XL_Import.Region, XL_Import.Section,
XL_Import.[Charter Fields], " & _
         "XL_Import.[Data Value], XL_Import.[Matrix 1 Table],
XL_Import.[Matrix 1 Attribute] " & _
         "FROM XL_Import " & _
         "WHERE (XL_Import.Section = 'DF" & x & "') AND (XL_Import.
[Data Value]<>'0') AND " & _
         "(XL_Import.[Matrix 1 Table]= 'ProductOption') AND (XL_Import.
[Matrix 1 Attribute]     ='package direction');"


If I paste this into an accesss query it runs fine.  Any ideas what is
going on?  I'm using Access 2003.


0
Comment
Question by:ecohouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20314414
The SQL looks OK, can you post the rest of your ADO code?
0
 

Accepted Solution

by:
ecohouse earned 0 total points
ID: 20314643
Thanks for the quick response.   I was able to figure out the problem.
It turns out that in this line:
(XL_Import.Section
I needed to put brackets around the word section.  to make it look like this:
(XL_Import.[Section]

I didn't know that Section was a keyword.  And because the query was running I didn't think that was the problem.  And the ambiguous Access error message didn't help either.
0
 
LVL 3

Expert Comment

by:SpeedFingolf
ID: 20508528
The error message is not ambiguous at all. The problem is the automation error is that the ADO recordset can not be created, since the SQL sentence that should be used to build and popullate it IS NOT A VALID SQL sentence and therefore, can not be parsed to get a result, which would be the recordset.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:ecohouse
ID: 20567773
I was able to solve this problem awhile ago on my own.  See my last comment.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20631439
Closed, 100 points refunded.
Vee_Mod
Community Support Moderator
0
 

Author Comment

by:ecohouse
ID: 20641676
Thanks.
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question