Solved

SQL Where Clause Causing Error

Posted on 2010-09-21
15
553 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
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33728128
try

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

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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
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.

 
LVL 51

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now