Jump to content

Error -2147217900 in Execute Query.vi after repairing Office 365 (?)


Recommended Posts

Hi All, Long-time lurker, first-time poster here.

As the title (hopefully) implies, I have an issue connecting to a database I've created. 

The problems started this week after coming back to look at the project after ~3 months. I know I had to repair my Office 365 installation last week and I'm wondering if that could be the cause but other than that I'm at a complete loose end.

Microsoft support say it's a LabVIEW issue, NI support say it's an MSDN issue. The official NI forum, after looking at similar posts, was even less helpful. So I thought I'd come and see if the nice guys at LAVA had experienced a similar issue.

I'm aware that the error code is a generic one and has multiple causes but I am simply writing a "SELECT [column] FROM [table];" statement to an 'Execute Query' vi. The error returned from my sanity checking vi is: 

(Code -2147217900) NI_Database_API.lvlib:Conn Execute.vi->Untitled 1<ERR>ADO Error: 0x80040E14
Exception occured in Microsoft Access Database Engine:  in query expression '[UserID]'. in NI_Database_API.lvlib:Conn Execute.vi->Untitled 1

The connection string returned is (without line breaks):

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=T:\PROJECTS\02-ACTIVE\119888 RF Power upgrade\Database\RFPU.accdb;
Mode=Share Deny None;
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=6;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=False;
Jet OLEDB:Bypass UserInfo Validation=False;

The BD for my sanity check looks like this:

ConnError.PNG

I'm using LabVIEW 2016 32-bit on a W7 Dell Ultrabook with Access2013 as part of our Office 365 installation. BTW the query executes fine in Access2013. Is that enough info?

Regards,

Boris

 

Edited by ChuckBorisNorris
Link to comment

No joy I'm afraid. The link took me to an Access2010 runtime which failed to install, probably because I already have Access2013 installed.

Looking back at the problem, I doubt this would have worked anyway since I have no issue connecting to the database and closing the reference at the end. It almost looks like a syntax error in the query but the query is so trivially simple... (quick look at original post), yep, nothing extreme going on here.

Link to comment
On 11/29/2017 at 6:29 PM, ChuckBorisNorris said:

I'm aware that the error code is a generic one and has multiple causes...

(Code -2147217900) NI_Database_API.lvlib:Conn Execute.vi->Untitled 1<ERR>ADO Error: 0x80040E14
Exception occured in Microsoft Access Database Engine:  in query expression '[UserID]'. in NI_Database_API.lvlib:Conn Execute.vi->Untitled 1

...

ConnError.PNG

Error -2147217900 is an SQL syntax error: https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z000000P83sSAC

Also, your error message points out that the engine doesn't like the expression '[UserID]'.

What happens if you try the following queries? What error does each query give (if any)?:

  • SELECT UserID FROM [Operator];
  • SELECT UserID FROM Operator;
  • SELECT * FROM [Operator];
  • SELECT * FROM Operator;
Edited by JKSH
Link to comment
12 hours ago, ChuckBorisNorris said:

I have tried all of the above (and without the ; as well), all give me the same error.

They give the same error code, but what about the detailed error message?

With your original query, the toolkit complained that it wasn't happy "in query expression '[UserID]'". What did it complain about in the other queries?

 

12 hours ago, ChuckBorisNorris said:

Surely you can agree there's nothing wrong with the original syntax though, and like I said, the query functions fine in Access2013.

I do agree with you that your syntax is valid.

I was hoping that trying different syntaxes and studying the toolkit's response will yield some insight into why the toolkit is tripping up. Even though the query functions fine in Access 2013, we don't know that the Database Connectivity Toolkit passed your query as-is to Access 2013.

Link to comment

JKSH,

The error message is the same, the only difference is either  "in query expression '[UserID]'""in query expression 'UserID'"  or  "in query expression '*'". The rest is the usual blah blah about undefined error codes.

Is there a way to check the call as it goes into Access?

Gribo,

I have a W7 (Windows 7) installation.

Link to comment
On 11/29/2017 at 11:29 AM, ChuckBorisNorris said:

I know I had to repair my Office 365 installation last week and I'm wondering if that could be the cause

Have you since tried reinstalling? Repair might have skipped repairing the database driver.

Other than that, there are a few clues on the web which indicate that the error code is caused by some invalid character in your query:

http://digital.ni.com/public.nsf/allkb/3BC28421B4761BCC8625710E006D76CB

https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z000000P83sSAC

http://digital.ni.com/public.nsf/allkb/D8FF30B4409602B386256F3A001FC96C

Since that is clearly not the case (unless you have some weird zero-width character in your query), you should try reinstalling the software.

You could also try running your VI on another machine to see if it works or not.

Link to comment

So, thankfully there were no zero-length characters in the query string.

I've just done a complete uninstall/reinstall of Office365 and the problem still persists, just to confirm that I've tried this avenue.

A bit more info: The built application, when accessing the same database from a different machine on the same network has no issues. The error is only reported on my machine.

So the syntax is correct when sent from one PC but not the other, maybe that's a clue?

Link to comment
1 hour ago, ChuckBorisNorris said:

So the syntax is correct when sent from one PC but not the other, maybe that's a clue?

It's a good sign. At least you can be sure that it'll work if you wipe your machine and install it from scratch :)

If you still want to figure out which part is causing your issues you'll have to eliminate factors one-by-one. Here are a few things you can try:

  • Log-in with a different user account and try to access the database
  • Copy the database to your local drive and try to use it with your application
  • Create a new database that has the necessary tables and try to use it with your application
Link to comment
On 12/4/2017 at 10:12 PM, ChuckBorisNorris said:

I've just done a complete uninstall/reinstall of Office365 and the problem still persists, just to confirm that I've tried this avenue.

A bit more info: The built application, when accessing the same database from a different machine on the same network has no issues. The error is only reported on my machine.

So the syntax is correct when sent from one PC but not the other, maybe that's a clue?

So a "good" installation of LabVIEW's DB toolkit and/or Office 365 are happy with your query. This suggests that something is different about this installation... but uninstalling/reinstalling hasn't repaired it so far.

I'm curious: You mentioned Office 365 and Access 2013. The current default version for Office 365 is Office 2016. Did you deliberately install Office 2013 via Office 365 (which requires jumping through some hoops, IIRC)? Or did you install the non-subscription version of Office 2013 separately from Office 365?

Anyway, one more thing you can try is to compare DLL versions. When the exception pop-up dialog appears, use ListDLLs or Process Explorer to see which DLLs are loaded (full paths and version numbers). Do the same on your "good" installation, and see if you can spot a difference.

Link to comment

Solved. 

I downloaded the process explorer mentioned above and saw that there was one Access-specific dll missing between the good and bad applications: ACEES.dll.

I copied this over from the good machine into the correct folder (C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14 on my machine, it may be different for other people) and everything appears to be running smoothly.

Thanks JKSH, yatta!

P.S. How do I mark this post as solved?

Link to comment
18 hours ago, ChuckBorisNorris said:

there was one Access-specific dll missing between the good and bad applications: ACEES.dll.

I copied this over from the good machine into the correct folder (C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14 on my machine, it may be different for other people) and everything appears to be running smoothly.

Thanks JKSH, yatta!

P.S. How do I mark this post as solved?

Interesting. I can't think of a reason why the DLL's missing (or why reinstalling doesn't bring it back), but you're welcome and I'm glad to hear you've found a solution!

I don't think this forum supports the "mark as solution" feature.

 

20 hours ago, ChuckBorisNorris said:

I didn't decide our company's policy on Office installations so I don't know how or why we have Access2013. Is that relevant?

It caught my attention because currently, Office 365 installs Excel 2016, Access 2016, etc. by default. I thought that perhaps reinstalling Office 365 didn't help you because your copy of Access 2013 was installed separately from Office 365.

Link to comment

Looking a bit further into it, I think the missing DLL was actually part of the Access run-time which I notice is installed separately. If I had repaired/uninstalled that then maybe I could have avoided all this hassle.

To anyone who has this particular error in the future, check to see if the Access run-time engine is installed separately (Start > Control Panel > Programs & Features) and if so, try fixing that first.

Our companies Office installation is a bit weird apparently, even the IT team think so. 

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By Deep_Blue
      Referring to comments in here, I'm looking for a solution to the font issue in the Icon Editor.
      Selecting different fonts does not seem to change the font in the Icon Editor, specifically in the Icon Text tab input.
      I've had no luck playing with the Tools=>Options=>Environment=>Linux=>Use pixel-based font sizes, it seems like whatever I select, the font won't change at all.
      This leads me to believe the issue is with the icon editor and the way it loads the fonts in Linux. I know that it works in Windows, but in Linux it seems as if the Icon Editor always defaults to a same font.
      It does change, but for the worse if I select any of the builtin(?) fonts, LabVIEW Application / LabVIEW Dialog / LabVIEW System - in the Icon Editor Properties.
      I've tried most of everything I found from internet plus then some. I've rebuilt the font cache, copied the small fonts .ttf file from windows etc. so I'm thinking the easy fixes are already exhausted.
      Environment: LabVIEW 2016 on OpenSUSE Leap 42.3.
       
      So, looking for help in LAVA forums for this.
    • By Gepponline
      Hi,
       I'm trying to insert some NULL values in a datetime field.
      In the example, the DATA_INSERIMENTO field has a non empty value and it works correctly but DATA_INTERVENTO doesn't accept NULL.
      If I use an empty string instead of null, the VI run without any errors but it fill the database field with a 1900-01-01 and it's not what I want.


       
      If I use the DB Tools NULL VI it gives me another type of error, maybe 'cause I'm connecting a variant to a cluster of string.
      If i use a Variant to Data VI for the NULL value it returns an empty string so not the result I need.
       

      If use the string you see in the label at the bottom of my diagram in SQL Server manager, it works correctly.
      How can I obtain the same result with labview?
    • By shahril
      Hi all,
      I need to query data from MS SQL server at different PC using LAN cable. Do you guys have any example to do this?
      Please help.
    • By shoneill
      My question relates to retrieving decimated data from the database.
       
      Given the case where I have 1M X and 1M Y steps (a total of 1000000M data points per channel) how do I efficiently get a decimated overview of the data?
       
      I can produce the correct output data be using
       
      Select X,Y,Z,Float1 from P
      WHERE X=0
      GROUP BY Y/1000
       
      This will output only 1x1000 data instead of 1x1000000 datapoints, one dimension quickly decimated  Problem is that is iterates over all data (and this takes quite some time).  If I do a different query to retrieve only 1000 normal points, it executes in under 100ms.
       
      I would like to use a CTE to do this, thinking that I could address directly the 1000 elements I am looking for.
      WITH RECURSIVE cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1000 FROM cnt LIMIT 1000 )WHAT GOES HERE?; So if I can create a cte with column x containing my Y Indices then how do I get from this to directly accessing
      Float1 FROM P WHERE X=0 AND Y=cte.x
       
      SELECT Float1 from P WHERE X IN (SELECT x FROM cnt) AND Y=0 AND Z=0
      Using the "IN" statement is apparently quite inefficient (and seems to return wrong values).  Any ideas?
       
      In addition, accessing even a small number of data points from within an 8GB SQL File (indexed!) is taking upwards ot 30 seconds to execute at the moment.  With 1.5GB files it seemed to be in the region of a few milliseconds.  Does SQLite have a problem with files above a certain size?
    • By SDietrich
      Name: libpq
      Submitter: SDietrich
      Submitted: 01 Mar 2014
      Category: Database & File IO
      LabVIEW Version: 2009License Type: Other (included with download)



      This is a package containing LabVIEW bindings to the client library of the PostgreSQL database server (libpq).
       
      The DLL version 9.3.2 and its dependencies are included in the package. This DLLs are taken out of a binary distribution from the Postgres-Website and are thread-safe (e.g. the call to PQisthreadsafe() returns 1). As of the moment the DLLs are 32bit only.
       
      The VIs are saved in LabVIEW 2009.
       
      So this package works out of the box if you have a 32bit LabVIEW 2009 or higher on any supported Windows operating system.
       
      Because this obviously is a derived work from PostgreSQL it is licensed by the PostgreSQL license.
       
       
      A few words regarding the documentation: This package is meant for developers who know how to use the libpq. You have to read and understand the excellent documentation for the library. Nonetheless all VIs contain extracts of that documentation as their help text.
       
      What's coming next?
      - adding support for 64bit
      - adding support for Linux (anybody out there to volunteer for testing?)
      - adding support for MAC (anybody out there to volunteer for testing?)

      Click here to download this file
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.