labview database problem: Create a database table from user defined fields

[pg1000]

I am trying to create a new table in an access database opened by an ODBC connection from information entered a table stored in the database, but every time I try to create the table, the Labview Database connectivity toolkit VI for creating a table throws a cryptic error

"Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition. in NI_Database_API.lvlib:Conn Execute.vi->NI_Database_API.lvlib:DB Tools Create Table.vi->DatabaseCreateHeaders.vi"

I think this is becuase I am trying to re-create the database  column variable type, but I am not sure what I am doing wrong.  Everything seems to be fine as the data types go, and everything works as long as I use a constant to create the database columns.  I even tried putting all of the information into the format labview defined, changed that to a variant, and redefined it as the labview database column type. 

I have attached a simplified VI and a picture of what I am trying to do, but I would appreciate any help I can get.  Hopefully I won't have pulled out all of my hair by the time some one replies!

[rolfk]

Have you made sure the table name you want to create does not already exist? You should probably first do a DROP <tablename> to be sure.

Rolf Kalbermatter

[pg1000]

I wish it were that simple.  I just created the database so there aren't many tables in there already.  I just tried it with a random name and still same error.

[PhillipBrooks · Accepted solution]

Make sure that your field names don't represent any reserved words

[pg1000]

Maybe I spoke too soon.  You were right about the column names offering some problems, but the error is thrown whenever I use the column label "Sensor 1".  Why is that?

[rolfk]

---

pg1000 wrote:
Maybe I spoke too soon.  You were right about the column names offering some problems, but the error is thrown whenever I use the column label "Sensor 1".  Why is that?

---

You might try to hack the DB Tools Create Table.vi to enclose the column name in [ ] brackets. The space in the column name is likely throwing the SQL parser off.

Rolf Kalbermatter

[khsay87]

Hi everyone. I am new to the database tool kit using labview. I am using labview 9.0f2 on Windows Xp. I am required to create a database (task is to create a table and to insert values into the table using Microsoft access 2007). I am trying to learn using the Create Database table.vi found with the software. I understand that need to create an access file and also had a mdl file which is name after it.(I had created them). Looking at the example given, I would like to add a few more variables, to be exact 6 more variables(therefore i would have 6 colum in my access file rite?). From the "connection information" the help information shows that it contain an 1D array of DB tools colum. ctl and also a cluster of 4 elements. the link to the access file, LabVIEW.udl shows that it restrict the colum in the access file.eg it has only stringcol,intcol,doublecol. I need to add more colum but cant add just like tt.

qn1: How to i add insert more varibles into the database so that it will apprears in access.

qn2: I cant drag and insert the "connection information" just to have more input. how do i do it?

qn3: where can i edit the info so that i can add more cluster into the access? when i drag out the input turn greyish.

[JeffL]

Hi khsay87,

You just need to make an array of clusters and pass it into the Create Table VI in the Column Information input. That will create the appropriate columns.

I'm not sure what you're referring to in questions 2?

[dj_tony]

NEVER_PUT_A_SPACE in column label. (:

[PhillipBrooks]

The link I provided for reserved words in no longer valid.

Here is a more useful link to Microsoft 

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql#odbc-res...