vba 在 Access 中生成表时设置字段大小（每列）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15085352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting field size (per column) while generating table in Access
提问by Gutanoth
I am trying to export my Database as an .dbf by using a VBA script, but the dbf requires the database to have certain values for the column size.
我正在尝试使用 VBA 脚本将我的数据库导出为 .dbf，但 dbf 要求数据库具有特定的列大小值。
When I leave the columns as they are in Access, I get an error saying
当我将列保留在 Access 中时，我收到一条错误消息
field will not fit in record
How can I set the column size for each column seperatly? Preferably while generating the table, so I don't have to do it manually everytime i generate a new table with queries
如何单独设置每列的列大小？最好在生成表时，所以我不必每次生成带有查询的新表时手动执行
And where do I set them? (in a Query or in SQL?)
我在哪里设置它们？（在查询中还是在 SQL 中？）
Thanks in advance!
提前致谢！
Edit:
编辑：
I have made sure that its the field size value that is giving me the error. I changed all the field size values manually by opening the table in Design View.
我已经确定它是给我错误的字段大小值。我通过在设计视图中打开表手动更改了所有字段大小值。
So now the second part of my question is becoming more crucial. Wether or not it is possible to set the field size while generating the table.
所以现在我的问题的第二部分变得更加重要。无论是否可以在生成表时设置字段大小。
Edit2:
编辑2：
I am currently using SQL in a query to create the table as followed:
我目前在查询中使用 SQL 来创建表，如下所示：
SELECT * INTO DB_Total
FROM Tags_AI_DB;
After the initial DB_Totalis made, I use several Insert intoqueries to add other rows:
初始DB_Total完成后，我使用几个Insert into查询来添加其他行：
INSERT INTO DB_TOTAL
SELECT a.*
FROM Tags_STS_ENA_DB AS a
LEFT JOIN DB_TOTAL AS b
ON a.NAME = b.NAME
WHERE b.NAME IS NULL;
If I set the column values in the DB_Totaltable while generating it with the Select intoquery, will they still have those values after using the Insert Intoqueries to insert more rows?
如果我在DB_Total使用Select into查询生成表时设置表中的列值，在使用Insert Into查询插入更多行后，它们是否仍然具有这些值？
Edit3:
编辑3：
I decided (after a few of your suggestions and some pointers from colleagues, that it would be better to first make my table and afterwards update this table with queries.
我决定（在听取了您的一些建议和同事的一些建议之后，最好先制作我的表格，然后用查询更新该表格。
However, it seems like I have run into a dead end with Access, this is the code I am using:
但是，似乎我遇到了 Access 的死胡同，这是我正在使用的代码：
CREATE TABLE DB_Total ("NAME" char(79),"TYPE" char(16), "UNIT" char(31),
"ADDR" char(254), "RAW_ZERO" char(11), "RAW_FULL" char(11), "ENG_ZERO" char(11),
"ENG_FULL" char(11), "ENG_UNIT" char(8), "FORMAT" char(11), "COMMENT" char(254),
"EDITCODE" char(8), "LINKED" char(1), "OID" char(10), "REF1" char(11), "REF2" char(11),
"DEADBAND" char(11), "CUSTOM" char(128), "TAGGENLINK" char(32), "CLUSTER" char(16),
"EQUIP" char(254), "ITEM" char(63), "HISTORIAN" char(6),
"CUSTOM1" char(254), "CUSTOM2" char(254), "CUSTOM3" char(254), "CUSTOM4" char(254),
"CUSTOM5" char(254), "CUSTOM6" char(254), "CUSTOM7" char(254), "CUSTOM8" char(254))
These are all the columns required for me to make a DBF file that is accepted by the application we are using it with.
这些是我制作 DBF 文件所需的所有列，该文件被我们正在使用的应用程序接受。
You'll understand my sadness when this generated the following error:
当这产生以下错误时，您会理解我的悲伤：
Record is too large
Is there anything I can do to make this table work?
我能做些什么来使这张桌子工作吗？
采纳答案by twoleggedhorse
UPDATE
更新
The maximum record size for Access 2007 is around 2kB (someone will no doubt correct that value)
When you create CHAR(255) it will use 255 bytes of space regardless as to what is in the field.
By contrast, VARCHARs do not use up space (only enough to define them) until you put something in the field, they grow dynamically.
Access 2007 的最大记录大小约为 2kB（毫无疑问有人会更正该值）当您创建 CHAR(255) 时，无论该字段中有什么，它都将使用 255 个字节的空间。
相比之下，VARCHAR 不会占用空间（仅足以定义它们），直到您将某些东西放入该字段中，它们才会动态增长。
Changing the CHAR(x)s to VARCHAR(x)s you will shrink the length of your table to within permitted values. Be aware that you may come into trouble if the row you are trying to insert is larger than the 2kB limit.
将CHAR(x)s更改为VARCHAR(x)s 您会将表的长度缩小到允许的值范围内。请注意，如果您尝试插入的行大于 2kB 限制，您可能会遇到麻烦。
Previous
以前的
The way to specify column lengths when generating the table is to use a CREATE TABLEstatement instead of a SELECT * INTO.
生成表时指定列长度的方法是使用CREATE TABLE语句而不是SELECT * INTO.
CREATE TABLE DB_Total
(
Column1Name NVARCHAR(255) --Use whatever datatype and length you need
,Column2Name NUMERIC(18,0) --Use whatever datatype and length you need
,...
) ;
INSERT INTO DB_Total
....
If you use a SELECT * INTOstatement, SQL will use whatever field lengths and types it finds in the existing data.
如果使用SELECT * INTO语句，SQL 将使用它在现有数据中找到的任何字段长度和类型。
It is also better practice to list the column names in your insert statement, so instead of
在插入语句中列出列名也是更好的做法，而不是
INSERT INTO DB_TOTAL
SELECT a.*
You should put:
你应该把：
INSERT INTO DB_Total
(
Column1Name
,Column2Name
,...
)
SELECT a.Column1Name
,a.Column2Name
,...
FROM ...
WHERE ... ;
回答by HansUp
In Edit2, you indicated your process starts with a "make table" (SELECT INTO) query which creates DB_Totaland loads it with data from Tags_AI_DB. Then you run a series of "append" (INSERT) queries to add data from other tables.
在Edit2 中，您指出您的流程以“制作表”( SELECT INTO) 查询开始，该查询DB_Total使用Tags_AI_DB. 然后运行一系列“追加”( INSERT) 查询以添加来自其他表的数据。
Now your problem is that you need specific field size settings for DB_Total, but it is impossible to define those sizes with a "make table" query.
现在您的问题是您需要为 设置特定的字段大小DB_Total，但无法使用“制作表”查询来定义这些大小。
I think you should create DB_Totalone time and set the field sizes as you wish. Do that manually with the table in Design View, or execute a CREATE TABLEstatement if you prefer.
我认为您应该创建DB_Total一次并根据需要设置字段大小。使用设计视图中的表手动执行此操作，或者根据需要执行CREATE TABLE语句。
Then forget about the "make table" query and use only "append" queries to add the data.
然后忘记“制作表”查询并仅使用“追加”查询来添加数据。
If the issue is that this is a recurring operation and you want to discard previous data before importing the new, execute DELETE FROM DB_Totalinstead of DROP TABLE DB_Total. That will allow you to preserve the structure of the (now empty) DB_Totaltable so you needn't fiddle with setting the field sizes again.
如果问题是这是一个重复操作并且您想在导入新数据之前丢弃以前的数据，请执行DELETE FROM DB_Total而不是DROP TABLE DB_Total. 这将允许您保留（现在为空）DB_Total表的结构，因此您无需再次设置字段大小。
Seems to me the only potential issue then might be if the structure of the source tables changes. If that happens, revise the structure of DB_Totalso that it's compatible again.
在我看来，唯一的潜在问题可能是源表的结构发生变化。如果发生这种情况，请修改 的结构DB_Total以使其再次兼容。
