使用 VBA 更改 Access 中多个表中列的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20056235/
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
Change data type of column in multiple tables in Access with VBA
提问by logicForPresident
I am fairly new to using VBA in MS Access and I am having trouble embedding SQL statements into VBA code. I have a database with almost 200 tables, and I would like to change the data type of one column (named lake) in each table to a "text" data type. I wrote the following code, but keep getting a syntax error for the ALTER TABLE statement.
我对在 MS Access 中使用 VBA 还很陌生，并且在将 SQL 语句嵌入到 VBA 代码中时遇到了问题。我有一个包含近 200 个表的数据库，我想将每个表中一列（名为 Lake）的数据类型更改为“文本”数据类型。我编写了以下代码，但不断收到 ALTER TABLE 语句的语法错误。
Public Sub changeDataType()
Dim db As DAO.Database
Dim table As DAO.TableDef
Set db = CurrentDb
For Each table In db.TableDefs
DoCmd.RunSQL "ALTER TABLE" & table.Name & "ALTER COLUMN [lake] TEXT(100);"
Next
Set db = Nothing
End Sub
Can anyone tell me why I'm getting the syntax error?
谁能告诉我为什么会出现语法错误？
Thanks,
Paul
谢谢，
保罗
回答by BWS
this statement will not be correct:
这种说法是不正确的：
DoCmd.RunSQL "ALTER TABLE" & table.Name & "ALTER COLUMN [lake] TEXT(100);"
if, for instance "table.Name" = "myTable", the resulting statement will look like this:
例如，如果 "table.Name" = "myTable"，则结果语句将如下所示：
DoCmd.RunSQL "ALTER TABLEmyTableALTER COLUMN [lake] TEXT(100);"
try adding a space to separate the name, like this:
尝试添加一个空格来分隔名称，如下所示：
DoCmd.RunSQL "ALTER TABLE [" & table.Name & "] ALTER COLUMN [lake] TEXT(100);"
