LabVIEW Error -2147217900 When Inserting Data Into an Access Database

Updated Oct 17, 2018

Reported In

Software

  • LabVIEW Database Connectivity Toolkit

Issue Details

Why do I get error -2147217900 when using DB Tools Insert Data.vi (Functions»Database) to insert data into an existing Microsoft Access database?

Solution

ypically, this error is generated when the name of the table or column contains a space (i.e. Table 1 or Column A) or other invalid characters. In the case of blank spaces in the name of a table or column, you should either remove the space (i.e. TableA) or replace it with the underscore character (i.e. Table_A). Changes will need to be made to these names in both LabVIEW and Microsoft Access.

Note: Verify that there are no invalid characters in the file path to the database. This can also cause this error.  Most commonly seen is the path Documents and Settings, which contains spaces and will cause errors.

While Microsoft Access allows the use of spaces in database table and column names, the LabVIEW Database Toolset uses the ANSI SQL format which does not allow the use of spaces in the arguments of SQL statements (most SQL formats do not allow spaces in argument syntax as well). For more information on valid SQL commands and syntax, please refer to the SQL Quick-Reference section of the Database Connectivity Toolkit User Manual

To insert and query table and column names with spaces in them you can add brackets to the name (i.e. [Table A]) using the concatenate strings function.

Note: This error can also be caused by the use of Access or Jet reserved words for column names. For example, Index, Date, Time, Position, or Timestamp. (See links to Microsoft KnowledgeBases for specific word lists and more information). Previously unreserved words may become reserved in later versions of Access or Jet causing working programs to stop functioning.

Note: Make sure to check that you are calling the correct database path. A good way to check what is in your database is using the Get Database Information.vi found in the Example Finder and searching database
 

Additional Information

The DB Tools Insert Data.vi inserts the data into a database table and/or columns specified by the strings wired to its table and columns inputs. Error -2147217900 is an SQL Syntax error that is generated when the syntax of these strings is not valid.

WAS THIS ARTICLE HELPFUL?

Not Helpful