VBA Access 2003 table with a variable number of Columns

Question

I have a table in Access 2003 VBA with 164 columns but the data I get has 181 column and it is possible that it will get bigger in the future. I need to know how to resize my table an to add extra colums during the runtime I know how to check how many colums I need so I just need to know how to resize my own table.

thanks

Answer 1

Might be something here. I'd probably create a new table based on the old one, and with the extra columns from data source. Then I'd populate the new table from the two data sources. I'd do this if for no other reason than it means I keep the old data safe while making the new.

Answer 2

Another option; rather than have a "short-fat" table with lots of columns, have a "tall-skinny" table with lots of rows:

Short-fat:

   ID
   FirstName
   LastName
   ...
   Amount

   1 Joe Smith   ... $123
   2 Bob Roberts ... $214
   3 Jim Black   ... $500

Tall-skinny:

   RecordID
   FieldNumber
   FieldName
   FieldValue

   1 1 FirstName Joe
   1 2 LastName  Smith
   ...
   1 n Amount $123
   2 1 FirstName Bob
   2 2 Lastname  Roberts
   ...
   2 n Amount    $214
   3 1 FirstName Jim
   3 2 LastName  Black
   ...
   3 n Amount    $500

This is good if you have a "stand alone" table that doesn't have to join with other tables, otherwise you're in for a world of pain.

Answer 3

There is not much to base this on, but it sounds like a design problem. I would look at the data that is driving these additional columns and see if you can create a column to store it.

If the data is really that volatile, i would consider creating a new table with Key, FieldName, FieldValue as your columns. This way you wont have to change your reports, queries, import routines every time the data changes.

Answer 4

I don't have the reputation to comment, but to extend the short-fat vs long-skinny argument, i think its a good idea to start there. then you can create a crosstab query using the field that has the old field names as the column header. then write a make-table off of that query and you will have your table.

Answer 5

the problem is that i don't have the rights to change the design the complete database is already created my job is just to impiment the reading of csv data but these dam... data need more colums than there were implimented by the previos programmer thats why i can't just delete the table and besides there are about 30 or 40 tables like the one i describet in the question thats why i asked for some kind of algorithm to change the amount of columns

but thanks for the fast awnser

Answer 6

If you are stuck with this poor design, you can use SQL to add columns as you need them:

strSQL="ALTER TABLE tblTable ADD COLUMN NewCol Text (25)"
CurrentDB.Execute strSQL,dbFailOnError

Or you can use the TableDef.

Answer 7

So are you looking for VB/VBA code to alter the table in the MDB? It sounds like that's what you're after -- something automated based on the incoming data. If you don't have permissions on the MDB file that's going to be hard to do, but I think what you're really saying is that you don't have the option to open the MDB in Access and modify it.