具有可变数量的列的VBAAccess2003表-VBAAccess2003tablewithavariablenumberofColumns

3  

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.

如果你有一个“独立”表,不必与其他表连接,这是很好的,否则你将陷入痛苦的世界。

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.

如果数据真的那么易变,我会考虑创建一个新的表,其中Key,FieldName,FieldValue作为您的列。这样,每次数据更改时,您都不必更改报表,查询和导入例程。

2  

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.

我没有评论的声誉,但是为了扩展短发和长期紧张的论点,我认为从一开始就是一个好主意。然后,您可以使用具有旧字段名称作为列标题的字段创建交叉表查询。然后从该查询中写下一个make-table,你将得到你的表。

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.

可能是这里的东西。我可能会根据旧表创建一个新表,并使用数据源中的额外列。然后我将从两个数据源填充新表。如果没有其他原因,我会这样做,这意味着我在制作新数据时保持旧数据安全。

1  

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

如果您遇到这种糟糕的设计,可以使用SQL根据需要添加列:

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

Or you can use the TableDef.

或者您可以使用TableDef。

0  

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

问题是我没有权利改变设计已经创建了完整的数据库我的工作只是为了破坏csv数据的读取,但这些大坝...数据需要更多的colums而不是previos程序员所表示的这就是为什么我不能只删除表,而且还有大约30或40个表,就像我在问题中描述的那个,这就是为什么我要求某种算法来改变列的数量

but thanks for the fast awnser

但感谢快速的芒果

0  

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.

那么你在寻找改变MDB表的VB / VBA代码吗?这听起来就是你所追求的 - 基于传入数据自动化的东西。如果你没有MDB文件的权限,那将很难做到,但我认为你真正说的是你没有选择在Access中打开MDB并修改它。