ADO Error: 0x80040E09 (or) -2147217911 in DATABASE Toolkits

[gnshmrthy]

<p>Hi friends,</p>

<p>             I`m trying to insert a 2D array of string values [1, 100, 500], [20, 40, 10] , [300, 0, 1] splitting the column by index array and to a for loop. Then  inserting the values in the database insert VI. For the first run of loop there is no error for second time I get the error. I`ve not added a space or other reserved words in the code. Were am I going wrong?    </p>

Edited November 13, 2013 by gnshmrthy

[LogMAN]

It is hard to help you if there is little to work with. The error is unknown to me, but it says that your database is read-only. So if you look into your UDL file, you can set it to read+write access (in the 'advanced' tab? - sorry, I've got a German system). Also shared access to Microsoft Access Databases is not possible afaik, so close the database in MSAccess before using it in LabVIEW.

 

If the error does not go away, maybe you can post some pictures of your source code, the UDL configuration and database.

[gnshmrthy]

Hi LogMAN,

 

here is my code. Since its large I didnt snippet from open and close. 

 

The file is read+write. I`m not opening the database while running the code by default so I hope that wont trouble here.

[Phillip Brooks]

I think it is because you have the option "create table" set to true.

 

On the first pass of the loop, it creates the table. On the second pass, the table exists and can't be overwritten.

 

I'm guessing you've been deleting the table between runs, or are creating a new DB each time.

 

If you need to create the table, then compare the loop iterator to zero, and use that as the input to create table.

Edited November 13, 2013 by Phillip Brooks

[LogMAN]

I think it is because you have the option "create table" set to true.

 

On the first pass of the loop, it creates the table. On the second pass, the table exists and can't be overwritten.

 

I'm guessing you've been deleting the table between runs, or are creating a new DB each time.

 

If you need to create the table, then compare the loop iterator to zero, and use that as the input to create table.

 

The help says: 'creates a table if a table does not exists...'

But I think the problem is based on that parameter anyways: According to the help, the size of a column is decided by the size of the input data, if the table is created by that function ('create table?' is set to TRUE). Assuming that your input data does have different string-length, the columns which are created in the first iteration could be to small for the next one. Thus the function tries an 'ALTER TABLE' which does not work for some reason. If that is true, try to pass a longer string to the first iteration. Also the method Phillip Brooks described is a good practice to prevent changes to the table after it has been created.

[gnshmrthy]

Hi Phillip Brook,

                       I tried the way you suggested but error pops up. 

 

Hi Logman,

                I hope the string length makes the problem. If the string length is same I don`t get an error. But since its an user input I can`t make the string length to be constant. 

[LogMAN]

Hi Phillip Brook,

                       I tried the way you suggested but error pops up. 

 

Hi Logman,

                I hope the string length makes the problem. If the string length is same I don`t get an error. But since its an user input I can`t make the string length to be constant. 

 

So the error vanished if the string length is the same. You should know, that databases have fixed length information on text columns, therefore preventing more input data to be written to it. (there are some exceptions, but I don't know about any in Access)

 

you should manually set the size for each column in the table, there is a function to do that: 'DB Tools Create Table.vi'. If you create a table with the expected number of rows and set the text length to 250 for example, the field should be big enough to contain any possible data the user could input. Also that way you can specify the column names and use them for the insert function. Be careful not to make the field to big, as each entry will use up the entire length (unused parts are filled up with 00 afaik)!

[gnshmrthy]

Hi LogMAN,

  Thats a great idea I`ll work on it. But I tried this way is it correct to program it.?

 

 

[LogMAN]

Hi LogMAN,

  Thats a great idea I`ll work on it. But I tried this way is it correct to program it.?

 

 

database.png

 

Your example should create a table with 2 columns (1x Numeric & 1x Text). The table name is '123' and will have 4 rows ( 1|abcd, 12|abc, 123|ab, 1234|a ). I'm not sure what you are trying to archive with it. Anyways, here is my example (did not test, but it should work if you open the database properly):

 

 

If I'm correct, the database should get a new table 'fancy_table' with three columns 'column_1', 'column_2' and 'column_3'. The table should also contain a single row with data ( 'first column' | 'second column' | 'third column' ). The maximum text length for each column is 50.

[gnshmrthy]

Hi Logman,

                 It works, kudos....    . In my post the values are stored as array so the size is constant (considered the size of an array in access) but you can`t view the data in the access (Long binary data) but can retrieve them if you need to call back or edit them.