Clever Geek Handbook

VBA Access 2003 table with variable number of columns

I have a table in Access 2003 VBA with 164 columns, but I have 181 columns and maybe it will get bigger in the future. I need to know how to resize my table a in order to add additional columns at runtime I know how to check how many stakes I need, so I just need to know how to resize my table.

thank

+1


source to share


7 replies


Another variant; instead of having a "short-lived" table with many columns, having a "high-precision" table with many 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 fine if you have a "separate" table that should not be concatenated with other tables, otherwise you are in a world of pain.

+3


source


There is little reason for this, but it looks like a design problem. I would take a look at the data that drives these extra columns and see if a column can be created to store it.



If the data is really unstable, I would consider creating a new table with Key, FieldName, FieldValue

as columns. Thus, you do not have to change reports, queries, import procedures every time the data changes.

+3


source


I don't have a reputation to comment, but for the short fat extension and the long skinny argument, I think it's a good idea to start there. then you can create a crosstab query using a field that has the old field names as the column header. then write a make table with that query and you will have a table.

+2


source


There may be something here . I would probably create a new table based on the old one, as well as additional columns from the data source. Then I will populate a new table from two data sources. I would do this if not for any other reason than it means that I keep the old data safe while creating a new one.

+1


source


If you are stuck with this bad design, you can use SQL to add columns as needed:

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

Or you can use TableDef.

+1


source


the problem is i dont have redesign permission full database already created, my job is just to back up reading csv data but dam data ... needs more columns than predecessor programmer intended that's why i can't just drop the table and besides, there are about 30 or 40 tables like the one I describe in the question that's why I asked some algorithm to change the number of columns

but thanks for the quick awnser

0


source


So, are you looking for VB / VBA code to modify a table in MDB? Sounds like what you want - something automatic based on incoming data. If you don't have the rights to the MDB file that would be difficult to do, but I think you are really saying that you have no way to open the MDB in Access and modify it.

0


source






More articles:


All Articles