How to use the DB Tools Insert Data VI with MS Access?

[Chris Barrett]

I'm trying to export test results data, from LabVIEW, into a Microsoft Access Database. The example on page 3-12 (figure 3-14) of the Database Connectivity Toolset User Manual works OK, PROVIDED that I am filling in every column in the Database Table. But I want one of the columns to be an auto-numbering column - so it cannot be written to. Hence I want to write to SELECTED columns in the table, not all the columns. Then the auto-increment field can update itself automatically. It would seem that the VI input "columns" might allow me to do this. But I can't make it work, and there is no documentation I can find to show me how to use it.

[JellyBeans]

Well first of all you must have that column set to "Auto Number" datatype in Access DB. The second thing is that you don't need to send data or the name of auto num column in INSERT VI. All you need to send is data except the auto number column.
One more thing is that the order of column names in array should be same as the cluster going into "data" input of the VI.

See attached image.

A Rafiq
National Instruments

[Chris Barrett]

Thanks for the prompt response. Your bitmap looks exactly like what I was attempting to do. But it kept generating error messages. Could you possibly send me a VI instead of a bitmap? I must be doing something silly.

[JellyBeans]

Attached is the zip file with VI and snapshot of my access table.

A Rafiq
National Instruments

[Chris Barrett]

Thanks, my VI is now working.
There was an "unknown error" Error-2147217900 - apparently a Syntax error in INSERT INTO statement. When I removed the spaces from my column names in Access (so that I had "Field3" instead of "Field 3" ), the error went away. Thanks for your help.

[Alain_S]

You lucky guy!!
I encounter the same problem even with no spaces in the column names. I had some spaces in the database name and path, but even when I removed them, the problem persist. Can you please tell me what's wrong?

Thanks a lot

See word doc for some screen shots

[Marcy B]

Hi!
I am also have the same problem. There are no spaces in the column headings, but I get error -2147217900 when attempting to insert data using the DB Tools Insert Data. Has anyone figured out what might be causing this to happen?
Thanks!
-Marcy

[Marcy B]

I had the same problem, but fixed it by removing fields named "date" and "time". There may be a format compatibility problem with these names. Changing them to "date1" and "time1" worked for me.

Hope that helps!

-Marcy

[jb7060405]

Yes, reminder to all!  Microsoft has about 40 or 50 reserved names that you CANNOT use for your Column headers in your Access database.  I am sure 'time' and 'date' are two of them.  'Data' is another one.  You can look up this list on Microsoft's msdn website concerning Access - just search for 'reserved names'.

[Parny]

From my research, I have clearly noticed and would highly recommend :

- Never name the columns of any table by using multiple words. If you want to use so, just dont give any space in between, make it a straight sentece.

ex :

sr no              should be   srno

name tag       should be   nametag

code name    should be   codename

Hope, few many would have noticed the same.