How to add records with 'Autonumber' property to a MS Access database using LabVIEW?

[S-Man]

I am trying to write to a MS Access database. Everything works fine as long as I add data to all records at the same time.
The database I am using have two tables (just for testing) with a 'one-to-many' relationship between the two. They both have a PrimaryKey with the data type 'AutoNumber'. From what I understand those numbers should be automatically generated when new data is written to the DB. I can't get that to work with LabVIEW.
I am using the function 'DB Tools Insert Data.vi'. According to the manual I should be OK with specifying the column I want to write to. If I specify all columns it works just fine but then the value of the 'PrimaryKey' is not Autogenerated.

I get
this error when not all columns are specified:
'Error -2147217900 occurred at Cmd Execute.vi->DB Tools Insert Data.vi->db_test.vi'

Can anyone help me figure out the 'AutoNumber' mystery?

[Crystal]

I'm not exactly sure what you mean by it not working. It would be good for you to attach an example including the database and the VI. I tried this with a simple example and it worked fine. Make sure the array of field names doesn't contain the name of the autonumber field. Also, that error is a generic syntax error. Look at the names of the fields and make sure they do not have spaces in the names or don't use reserved words. Use the underscore character in field names instead of spaces.

Crystal

[S-Man]

My description was probably not that good.
Here are the code + DB + udl file.
There are some comments in the code.

Put the udl file in a directory of your choice and then modify the 'connection information' path on the front panel.

I am running LV7. Let me know if you need a LV6.1 version.

[S-Man]

I need to make a correction.
You need to make a new udl file that points to the db in order to get it to work.
Sorry about that!

🙂

[Crystal]

I made 2 small changes to make this work:

- in the database tables, "timestamp" is a reserved word so I changed it to "timestamp_val".

- in the VI, I added a delete from array to remove the autonumber field from the columns array (and the first item from the cluster).

Let me know if you have further questions or comments.

Crystal

[S-Man]

It seems to be working fine now. Thank you. How did you find this out? I searched everywhere and found nothing.
Why does it work when you take out the autonumber field but it desn't work if I don't provide it from the start?

Anyhow, thank you again for solving my problem.
:beer; for you and a Happy New Year.
🙂

[Ben]

Hi Crystal,

I would like to humbly request that you become an enthusiast (technically I beleive you would qualify as an "Expert"). This would allow us to subscribe to your answers and recieve automatic updates whenever you post an answer or a response.

S-Man,
"How did you figure this out?" I believe this is the same "Crystal" that wrote the original LV SQL tool kit.

Ben

[Crystal]

Hi, Ben

Thanks for your comment, I'm flattered. I honestly don't know how one gets marked as an "enthusiast" but I am on the LabVIEW R&D team and the Database Connectivity Toolset is one of the products I'm responsible for. (The original SQL Toolkit was written by an alliance member many years ago and is no longer supported.) The world of databases isn't trivial and I try to help when I can.

Thanks again,
Crystal

[S-Man]

That explains it! 🙂

I'd like this to be better documented in the LV Manuals. I looked everywhere but couldn't find anything about my problem.

[Alex77]

I applied the same approach as you guys have described, however my problem starts before I get to autonumber.
I have an issue feeding column (data) from "DB tool list column VI" to "DB Tools insert data VI" .So when  I connect  column output of "DB tool list vi" to column input of "Insert data Vi " I get an error message "exception occurred in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.."

However, when I don’t connect column between those two vias, basically leaving it blank ( it treats it as filling all the columns in database) and it works fine that way.
I talked to NI engineer and he couldn't figure out exactly what causes that problem, ”DB Tools List column vi" outputs correct column info, which corresponds correctly to my created data base. I am suspecting it could have something to do with ODBC driver or version of Access that I am using.

Access version is 2003, and I tried ODBC driver of 2002 & 2004 with the same results.

Any help would be appreciated