Solved

SQL Where Clause Causing Error

Posted on 2010-09-21
15
552 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

20 Experts available now in Live!

Get 1:1 Help Now