Creating Tables in Access Database - Field Size

[jones]

Hello All,

I have a situation where I am writing data (numeric and text strings) to a database (using the Database Connectivity Toolset 1.0.1) and creating new tables within my .mdb file dynamically when needed. For text fields, the "Field Size" is set to the number of characters in that field when the table is created. Unfortunately, this field size can be too small for later entries written to the table, thus the later entries get truncated. Empty text fields result in a field size of 255, which is fine. I would be satisfied if all field sizes were set to 255. I drilled down into the VIs in the toolset and found were LV is setting the field size, but I would REALLY like to avoid customizing this VI and having to move the VIs out of VI.lib and into my folders and the headaches that go with it.

My first idea was to insert a row to the table that was completly empty. Here's the problem...my VI only has a varient as the data input; I don't have the cluster that the variant was converted from, so I can't create a dummy row with no data, since I don't know what kind of data is in each column.

1) Is there a way to empty the text string data out of the variant without having access to the cluster from which is was created? or

2) Is there a way to force the field size of the text string columns to something other than the size of the the first entry when creating the table?

Thanks!

[xtal]

Why don't you use the Execute Query VI and create the tables with the sizes you want from the beginning? For example, if you input the following string into the Execute Query:

create table mytable (field1 text(255), field2 datetime, field3 text(255), field4 byte);

would create a table named mytable with four fields where both text fields are set to 255 characters.

The top-level database VIs are meant for very easy storing and access of data. Once you start changing things like field sizes and other things, you should start using the advanced VIs.