Database issue new field in table DB Tools Select Data.vi throws -2147217904

[Randall_T]

This application is being used to retrieve data from an Access database. Recently added a field to the database table. Queries to this table continues to work unless adding the new field to the query. The error code -2147217904 Possible reason is 0x80040040E10, Invalid string: Specified field does not exist or contains unsupported character.

I have traced this to the Conn Execute.vi vi. everything is good, no errors until the sql query statement is sent to  the command.  I can copy the sql query statement from the probe which causes the error then open the database with Access create a query in Access by pasting the SQL statement. The query will work in Access retrieving the desired information. Back in LabVIEW if I substitute the new field in the query with any of the other fields then it works. Have changed the name of the field in data base and query statement get the same error results in LabVIEW but not when using copy of sql in Access.  It appears that LabVIEW ODBC driver is not recognizing any new field in the data table. I use a specified DSN file which has not changed but am not sure that it should change. I will add the DSN file to the zipped file. A standard text editor can view the DSN file. 

The SQL statement being sent is below.  The first file [SerNum] is the offending field. It exist in the database. Changing SerNum to Model while leaving Model as the second field will work returning model twice.   Any help is appreciated.

SQL:   SELECT [SerNum],[Model],[Manufacturer],[Refrigerant],[Frequency],[Mass Flow 1],[Mass Flow 2],[Mass Flow 3],[Mass Flow 4],[Mass Flow 5],[Mass Flow 6],[Mass Flow 7],[Mass Flow 8],[Mass Flow 9],[Mass Flow 10],[Power 1],[Power 2],[Power 3],[Power 4],[Power 5],[Power 6],[Power 7],[Power 8],[Power 9],[Power 10],[Capacity] FROM CompDat Where Model = '2K15S236UUA' AND Frequency = 60

[aputman]

Why does the db path in the DSN file have double slashes?

[Randall_T]

That is a good question. The path to string function puts the double \\ in the path. I tried both ways and it doesn't appear to make a difference. This did get me to thinking about the rest of the dsn file because the application is several years old I didn't remember exactly what the rest of the values were or where they came from. 

The DB Tools Open Connection.vi has an input for dialog prompt (F).  I changed this to True and a dialog comes up allowing you to select the database driver, data source with path, user, (admin) and a test connection button. After connecting to the data base it created a file called "Text File of Dsn Type.txt"  This file exactly matched the Dsn file I attached previously.

I appreciate the question. I was wondering if somehow LabVIEW was ignoring the DSN file and using some previously cached settings before the additional field was added to the data base. This exercise proved the connection was to the current data base file and using the DSN settings from the file.

Still same results. A query with the new field name generates an error. Otherwise all other queries function as expected.

[Randall_T]

Okay need to amend my previous message. 

The DB Tools Open Connection.vi has an input for dialog prompt (F).  I changed this to True and a dialog comes up allowing you to select the database driver, data source with path, user, (admin) and a test connection button. After connecting to the data base it created a file called "Text File of Dsn Type.txt"  This file exactly matched the Dsn file I attached previously.

Although using the dialog option does connect to the data base and functions as described. The text file I thought was created during the process was actually a copy of the original dsn file. For those who read the previous post and are trying to locate the file. My apology.

[FireFist-Redhawk]

Why don't you drop a List Columns.vi in there and see what all columns LabVIEW thinks are there.

[Randall_T]

Excellent suggestion about the columns. I have an application enhancement coming up that I may be able to use that with as well. Thanks Redhawk.

[Randall_T · Accepted solution]

Okay, I have the program now working. I do believe the issue was some way connected to the Access data base and my specific system. The data base file I use for this application is in the file mentioned in the DNS file. This file is the only data base file connected to through the LabVIEW application.

 I do have a separate Access database file that is used as a front end it has a user interface which allows taking data from other sources and inserting into the table used in the LabVIEW application mentioned above. This Access interface is linked to the table in the applications database. I decided to open this Access front end to see if the linked table with the new field had caused any issues with this front end as well. It did not and the new field showed up in the linked table as I expected.  However after doing this and closing the files the LabVIEW application now works.

The only thing I can guess is that Access hadn’t resolved the changes in the linked table and couldn’t recognize the field in the ODBC Access driver. Purely conjecture on my part. For now I believe the problem is resolved even if I am not quite sure what fixed the issue. The application is intended to run on other test stations where only the application data base file exist, not the Access frontend mentioned here.  Should I find the problem occurs on the test stations I will update the post.

For those who read this post and have attempted to offer suggestions I appreciate your responses. Thanks.