excel database management

excel database management

Working at my excel file macros

there is titles with sub titles in a  few titles lines in excel (2)
i programmed this in a static way
now i want to reprogramm it with a recursive sub to generate sub titles everytimes a new array is in an array

this way the excel table will be generated with titles and sub titles, infinitly (recursiveness)
this sub have to be done object so it can be used by any part of my programming

including displaying other tables

ideally, one objecty should contain all arrays that will generate all titles and sub titles recursively

i also have to think about futur use, like editing thoses table and adressing the right column name while doing it even though there will be sub titles for evey columns, only the last sub title will be the column name used to access the data, the rest of the main titles will be there only for appearence and clarity

dynamic entry! Infinite join query based on column names

Game on!

My last baby, a vb code to build a query made of arrays

suppose you want to update information in a table
but to make that decision you need info from another table, from another application that is read only
both table are related by an id column

so there is:
arabd(1) array of database
aratab(1) array of tables
aracol(1) array of columns

(read only)
aradb(0)=array("db01")
aratab(0)=array("product")
aracol(0)=array("id", "productacronym","datafromautocadetc")

(write/read)
aradb(1)=array("db02")
aratab(1)=array("product")
aracol(1)=array("id","id_product","description")
aralin(1)=array("left join")

the vb code, will analyse the second db
it will find a column that contain the table name in preceding database
in this case:
"id_product" --> contain product, wich is a table name in the database element in array just before the actual table

so the link will be made with a left join from (on) column "id_product" to "id" in product table, in db01 database

the query will be generated to make this left join and executed

that will result in a table containing columns from both tables / databases

now the moment of truth, yes, i know you can do this with a view in sql

BUT when come the moment to write in one of the database, but not the other, the view cannot do it
you will need a specific update or insert to do that

since all our table parameters are in arrays, we can generate another query to write back in one table with the right id to change one record related to another table, in witch we have no right to write (db01, tab01)

so the acronym would be a column read only from another application
the description would be in a table where we have write access

of course if we add more databases arrays, more table arrays, and more columns arrays, the query generated will be longer, but still work, up to infinite! Or up to how much ram your sql server have ;)

next step:
write the code to generate the write query that will write in only one table to update the record we can or have the right to write

this is all done in excel, to edit databases from different applications. Some we want the data from as complementary information to make the modifications, but have no right to write in

of course for this query to work you will have to respect a certain naming convention
always have an id, always have an id with table name after it in next database you want to join with