Database insert fails when VI converted to executable

[Brent_Gordon]

I am using the Database Toolkit to insert a related record into a table based on a key from a different table.  The process works fine in LabVIEW.  However, when I build the application it no longer works.  The error message is:

 Cmd Execute.vi->DB Tools Insert Data.vi->CalCheck.vi<ERR>Exception occured in Microsoft JET Database Engine, You cannot add or change a record because a related record is required in table 'Cal_Checks_New'..  in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->CalCheck.vi

with an error number of  -2147467259 (0x80004005).

I've checked the database and the related record is there.  I'm guessing that what is happening is that the Jet engine is delaying the write of the first record so that when I try to add the related record, the first record isn't there yet.  I'm using LabVIEW 7.1.1. with Database Toolkit 1.0.132 talking to an Access 2000 database using the Jet 4.0 database engine on Windows Vista Business.

Create_Record shows where I create the first record and get the Index (Xref) for later use.  Add_Related_Record shows where the related record is added and the program files.  There are several steps that can take a minute or two between the two cases, including waiting for operator input and data acquisition.

Just to repeat, the program works as a VI and fails as an executable. 

[GGT]

Brent,

I've never used LabVIEW with a database but I'm replying to you because of something I found out only this afternoon. A work colleague had a top level VI that ran fine but its executable misbehaved (control blink colours were different between the two). Googling revealed that not all VI settings are transferred to an executable (as you might think/hope), but some aspects of an executable's behaviour depend on what's in its ini file (always wondered what the executable ini file was for!). To get the database aspect of your LabVIEW source code to work is there something you've had to select in Tools>Options? If so, the ini file entry for that may need to be put in your executable's ini file. Just a passing thought.

Regards, GGT.

[Brent_Gordon]

I copied the contents of the labview.ini file into the program's ini file; it didn't fix the problem.  Thanks for the suggestion.

The only useful thing from labview.ini that I typically put into my programs is HideRootWindow=True. 

[Brent_Gordon]

The executable built on Vista fails on both Vista and XP systems; an executable built on the XP system works.

[mikeporter]

How are you opening your connection to the database? What is the contents of your UDL? Are there any check constraints on the table you are inserting the data into? Are there any referential integrity constraints on the table? Would there be major heartache if you moved your database to something like SQL Server Express (free)?

Mike...

[Brent_Gordon]

Mike,

Here are your questions with my answers.

How are you opening your connection to the database?

I'm opening the database with the standard LabVIEW "DB Tools Open Connection.vi"; see the images in my first posting.

What is the contents of your UDL?

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\All Users\Documents\Brent\Strain_Dev.mdb;Persist Security Info=False

Are there any check constraints on the table you are inserting the data into?  None

Are there any referential integrity constraints on the table?  There is a one-to-many relationship from the Indx field in Cal_Checks_New to the Cross_Ref field in Cal_Readings.

Would there be major heartache if you moved your database to something like SQL Server Express (free)?  I'm not familiar with SQL Server Express.  I've done some work with regular SQL Server and I'd rather stay with Access.  Also, the computers the program is running on are old, underpowered, low memory, XP Home systems.  SQL Server Express might be too much for them.

I'm beginning to think the problem is caused by Vista's version of MDAC.  As I mentioned in a previous post, building an executable on the XP machine works.  Also, when I copy any database VIs between the Vista and XP machines, they recompile.

Brent 

[ColeTrain]

Brent,

This error has been reported by Microsoft when writing to a database using an automation program.  While none of their documentation mentions LabVIEW specifically, it looks like this error is thrown when trying to access something that is not currently available lending credibility to your previous thought of the first entry is not there yet.  

Also, when you say it recompiles if you copy the VIs from Vista and XP, what specifically is recompiling?  Do you mean the Database VIs or your executable?

[Brent_Gordon]

Cole,

This error has been reported by Microsoft when writing to a database using an automation program.  Do you have a Microsoft Knowledge Base number? 

....lending credibility to your previous thought of the first entry is not there yet.  The record is making it into the database.  After the program writes the first entry, the program waits for operator input and then does some data acquisition.  Once the program reached the point where it waits for operator input, I opened the database and verified the existence of the first record.  I then went and checked email and did other stuff for about 15 minutes.  I then let the program continue; it still failed when trying to insert the related record.

...what specifically is recompiling?  Do you mean the Database VIs or your executable?  The database VIs.  I'm on a different computer now, so I can't reproduce the message exactly, but the only difference from the attachment below is the names of various database toolkit VIs instead of CalCheck.vi

The more I look at this, the more I think it is an MDAC issue (dll hell).  The XP machine has MDAC 2.5, the Vista machine has the Vista version of MDAC.  I vaguely remember that when I originally installed the database toolkit on the Vista machine I had some issues.  However, it has been over a year so I don't recall the details.

Brent

[ColeTrain]

Brent,

I found the mention of the error in KB286126.