Database AddNew recordset and Update using the database connectivity toolkit

[ben64]

I'm new to database and currently working on moving a test software from VB to LabVIEW 2012. The database is using MS Access 2010.

In the VB software a new recorset is created in a table using the AddNew statement and then the Update statement. The test ID (AutoNumber field) value is then retrieve. Is there a way to do this in LabVIEW? The DB Tools Insert Data.vi is using the INSERT method and the recordset reference is closed within the vi. Currently I'm using the DB Tools Execute Query.vi with the following statement to retrieve the ID value :"SELECT MAX(ID) FROM Test" but I don't like the fact that I'm losing the link with the recordset I just added and I can't be 100% sure that the max ID will correspond to the recordset I just added.

Is there a way to do this using the Database Connectivity Toolkit?

Thanks,

Ben64

[ben64]

Attached is a first attempt and the error message I get.

Ben64

[dkfire]

Don't ever do that with a database. ( And MS Access is not a real database  )

It is not a good way in LabVIEW and it is not a good in VB.

If you what to insert something into the the database, insert ALL you data for that row once, don't use an update for that. 

And remember to always have a way to uniquely identify each row.

I don't have LabVIEW access for the next week, but I will try to come back when I get access to LabVIEW to help you.

You know that you can get a free database from MS ?

http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

[Yamaeda]

You Insert a new value, then Select with the table keys to get the autonumbered field back for later reference/update.

/Y

[ben64]

---

@Yamaeda wrote:

You Insert a new value, then Select with the table keys to get the autonumbered field back for later reference/update.

/Y

---

@Yamaeda, what do you mean exactly by "Select with the table keys? Do you mean using a SELECT statement with a WHERE row condition where the fields are equal to the ones I just Inserted?

The VB the code used starts with JetRsTest = New Recorset (I think this created the recordset in local memory) followed by  JetRsTest.Open SELECT * FROM Test, then .AddNew.Fields ( ...) (all the table columns) and finally  JetRsTest.Update to update the database with the new recordset. To get the new test ID the following call is made  JetRsTestID = .Fields("ID").Value. This seems like the logical way to do this.

Ben64

[britehappy]

Ben,

Were you able to get this working? I inherited some old code that is inserting 10,000 rows one by one into Access and its pretty slow. Trying to go this route and speed things up but I'm not too familiar with this methodology.

[britehappy]

For Ben and anyone else who is attempting to get this to work I finally got around the errors mentioned above and several others. I was not able to make this working using the Rec getData.vi as Ben was but rather had to go through the OpenDatabase -> OpenRecordset method. Attached is a VI that works to insert records into a database. I haven't played around with the options on OpenRecordset or Update enough yet to understand the nuances of how it handles conflicts so make sure you do that if it is important to you.

[cao_flex]

Hi, I have labview 8.5 and I cant open the vi, could you provide the picture of the code (addnew and update) connectivity toolkit.

Thanks,  

[hnesmith]

cao_flex, 

I'm not sure if this was the VI you were looking for (addnew and update), but the attached version can be opened with LabVIEW 8.5.

[cao_flex]

The VI help me to find best way to update my Database.

Thansk so much for your support.