Solved

SQL Where Clause Causing Error

Posted on 2010-09-21
15
558 Views
Last Modified: 2012-05-10
Why does the code below generate the following error when the WHERE clause is based on a numeric field?

    Run-time error '-2147217900 (80040e14)':
    Internal OLE Automation error

If I change the WHERE to a text field, then the code works fine.  I've tried Access 2000, 2003, and 2007 database types.  I've also tried a DSN connection.  It makes no difference.  If the WEHRE clause is based on a numeric field it fail every time.  This used to work.  
Sub Example()
    Dim adoCon As ADODB.Connection, adoRec As ADODB.Recordset
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\eeTesting\Database4.accdb;Persist Security Info=False;"
    Set adoRec = adoCon.Execute("SELECT * FROM Table1 WHERE SomeNumberField=10")
    Set adoRec = Nothing
    Set adoCon = Nothing
End Sub

Open in new window

0
Comment
Question by:David Lee
[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
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33728128
try

Set adoRec = adoCon.Open("SELECT * FROM Table1 WHERE SomeNumberField=10")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33728154
Sub Example()
    Dim adoCon As ADODB.Connection, adoRec As ADODB.Recordset
    Set adoCon = CreateObject("ADODB.Connection")
    set adoCon.Connection ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\eeTesting\Database4.accdb;Persist Security Info=False;"
    Set adoRec = adoCon.open("SELECT * FROM Table1 WHERE SomeNumberField=10")
    Set adoRec = Nothing
    Set adoCon = Nothing
End Sub
0
 
LVL 76

Author Comment

by:David Lee
ID: 33728255
That generates an error

    Compile error:
    Expected Function of variable

and highlights "open"
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 76

Author Comment

by:David Lee
ID: 33728291
Tried this approach too, but it generates the same error as the one I initially reported.
Sub Example()
    Dim adoCon As ADODB.Connection, adoRec As ADODB.Recordset
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\eeTesting\Database4.accdb;Persist Security Info=False;"
    adoCon.Open
    Set adoRec = New ADODB.Recordset
    adoRec.Open "SELECT * FROM Table1 WHERE SomeNumberField=10", adoCon
    Set adoRec = Nothing
    Set adoCon = Nothing
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33728348
Sub Example()
    Dim adoCon As New ADODB.Connection, adoRec As ADODB.Recordset

    adoCon.Connection ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\eeTesting\Database4.accdb;Persist Security Info=False;"
    set adoRec=New ADODB.Recordset
    adoRec.activeconnection=adoCon
    adoRec.open("SELECT * FROM Table1 WHERE SomeNumberField=10")
    Set adoRec = Nothing
    Set adoCon = Nothing
End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33728473
Is "SomeField" actually defined as a number or Text in the source table?

If it is defined as Text, then you will have to reference it as a string.
    "SELECT * FROM Table1 WHERE SomeNumberField='10'"
...no other real way around this...

Other notes:
Are you compiling your code on a regular basis>
(in the VBE , click: Debug-->Compile)

Are you running the compact/Repair utility on a regular basis?

;-)

JeffCoachman
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33728751
Doesnt make sense that you cant extract from a numeric field, its either a little corrupt or dodgy code as Jeff as pointed out. Most likely is incorrect code. In your first post you did not associate the connection with the recordset

Also there is no need to use CreateObject if you are using early binding. Just instantiating it with the New keyword is good enough.

You should also be closing your recordset and connection properly
Dim adoCon As ADODB.Connection
    Dim adoRec As ADODB.Recordset

    Set adoCon = New ADODB.Connection
    
    adoCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\eeTesting\Database4.accdb;Persist Security Info=False;"
    adoCon.Open
    
    Set adoRec = New ADODB.Recordset

    adoRec.ActiveConnection = adoCon
    
    adoRec.Open "SELECT * FROM Table1 WHERE SomeNumberField=10"
    Debug.Print adoRec!SomeField
    
    adoRec.Close
    Set adoRec = Nothing

    adoCon.Close
    Set adoCon = Nothing

Open in new window

0
 
LVL 56

Expert Comment

by:HainKurt
ID: 33729478
is tehre any way to post the sample database that creates this problem, with the query that gives error...
0
 
LVL 76

Author Comment

by:David Lee
ID: 33756160
It's not one database, it's any query with a where clause that keys on a numeric field. It's not the code either. The code works perfectly as long as the where clause keys on a character field.

Stumbled across this page where someone had a similar problem (although on a web page, mine isn't on a web page) and found it was a permissions problem.  That got me to thinking about security settings.  I was running the code under a non-priv account.  Switched to using a priv account and it works fine.  Something about the difference in permissions between the two accounts.  Strange stuff.  hard to fathom how privleges can affect the ability to query against a numeric field.
Thoughts?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33758615
<security settings.  I was running the code under a non-priv account.>

Security where?
Access User Level Security?
SQL Server?
Network?

Strange indeed...

;-)

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33758933
Reading that page, it just doesn't make sense why a numerical field would fail. You would think if it was going to fail then it would fail with everything. It can't be something as simple as indexing?  Maybe its indexed fields being used or updated, I dunno.

Did you try a run as when logged into the non-priv acccount? It should have the same affect as logging into a priv-account. Just saves the hassle of logging out and in again. If no joy then perhaps thats the only option you may have going forward.


0
 
LVL 76

Author Comment

by:David Lee
ID: 33921114
"Reading that page, it just doesn't make sense why a numerical field would fail"
I agree.  But it does fail.  

"Did you try a run as when logged into the non-priv acccount?"
Yes, and that works fine.  The issue boils down to which account the code runs under.  Everything works fine when I run the code from a privileged account but fails when I run it from a non-privileged account.  I don't work for our IT security group and therefore don't know all the restrictions placed on the non-priv account.  

Having discovered that the account makes the difference I have a workaround.  Was hoping that one of you had experienced this yourself and could shed light on why the account makes a difference.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33922286
Sorry I could not help more...
:-(
0
 
LVL 76

Accepted Solution

by:
David Lee earned 0 total points
ID: 33982620
No worries, Jeff.  It's apparently something unique to my environment.
0
 
LVL 76

Author Closing Comment

by:David Lee
ID: 34026631
The problem appears to be the result of an unusual security configuration in my working environment.  There doesn't appear to be a solution other than the one I had already discovered which is to run the code from a privileged account.  I appreciate everyone's help and suggestions,
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month9 days, 3 hours left to enroll

617 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