Improve company productivity with a Business Account.Sign Up

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

SQL Where Clause Causing Error

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
David Lee
Asked:
David Lee
  • 6
  • 3
  • 3
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

Set adoRec = adoCon.Open("SELECT * FROM Table1 WHERE SomeNumberField=10")
0
 
Rey Obrero (Capricorn1)Commented:
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
 
David LeeAuthor Commented:
That generates an error

    Compile error:
    Expected Function of variable

and highlights "open"
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
David LeeAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
rockiroadsCommented:
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
 
HainKurtSr. System AnalystCommented:
is tehre any way to post the sample database that creates this problem, with the query that gives error...
0
 
David LeeAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
rockiroadsCommented:
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
 
David LeeAuthor Commented:
"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
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry I could not help more...
:-(
0
 
David LeeAuthor Commented:
No worries, Jeff.  It's apparently something unique to my environment.
0
 
David LeeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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