Saving unknow contents from ListBox

Databases
1

I started to implement a Save (in SQLite db file) in my List manager who uses a ListBox to store the user data in.

The question was about how to create the Table(s): both Header and Data Tables.

Why ?

Because their holding is unknow (for the Header strings) and their number of columns is also undefined (unknow) at Table creation and Record Population / Insertion.

How do I do that ?

At first, I wanted to use the header strings to name the columns, but “Date (Start)” - for example - is bad - and “Date (End)” is considered as a duplicate of “Date (Start)”.

Instead of starting to search how to code the string headers (say replace spaces with an underscore character and see if other characters can be used [’"[{<, etc.]), I decided to use a different build way, something like:

For ColIdx = 0 to ColCnt TheHeader = TheHeader + "Header_" + Format(ColIdx,"000") + " Text, " Next

to build the sql string to create the table and:

For ColIdx = 0 to ColCnt HeaderRec.Column("Header_" + Format(ColIdx,"000")) = LB.Heading(ColIdx) Next

To add the ListBox Heading in the Header Table…

Same kind of stuff for storing the ListBox Rows…

It seems to work for save and open (the .sqlite file).

At last: I create a Table to story the ListBox Header strings and another Table to store the ListBox Rows (in both open and save).

What is your opinion on this design (to open - save unknow formated data) ?

2

XML or JSON might be my first choice here.

Date (Start) Date (End) Age
value1 value 2 value 3

Iterate through the columns to get your Attribute names

Date (Start) Date (End) Age "Date (Start)"="Value1" "Date (End)" = "Value2 ...

The collection of Headers tells you what the fields are called.
For each row, you can request the value for each of the header names.

3

Or maybe a document-storage database might be worth considering instead of a SQL-based database? Jeff’s suggestions are two of many options in this regard

4

Jeff, Alex:

Thank you for your answers.