从 Excel VBA 创建 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/927349/
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
Create a SQL Table from Excel VBA
提问by Mat Nadrofsky
The problem is that I cannot get the table name that was entered into the variable, "tblName", to be used instead I get a correctly named database with a table named "tblName".
问题是我无法获得输入到变量“tblName”中的表名以供使用，而是获得了一个正确命名的数据库，其中包含一个名为“tblName”的表。
Is there some way to pick up the name in "tblName" or some way to change the name once it is created with a name th user enters?
是否有某种方法可以在“tblName”中获取名称，或者在使用用户输入的名称创建名称后以某种方式更改名称？
Private Sub CreateDatabaseFromExcel()
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim tblName As String
'Set database name in the Excel Sheet
dbPath = ActiveSheet.Range("B1").Value 'Database Name
tblName = ActiveSheet.Range("B2").Value 'Table Name
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
'Create new database using name entered in Excel Cell ("B1")
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing
'Connect to database and insert a new table
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
.Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _
"[RTNumber] text(9) WITH Compression, " & _
"[AccountNumber] text(10) WITH Compression, " & _
"[Address] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[ProvinceState] text(2) WITH Compression, " & _
"[Postal] text(6) WITH Compression, " & _
"[AccountAmount] decimal(6))"
End With
Set cnt = Nothing
End Sub
回答by Mat Nadrofsky
Change this line:
改变这一行：
.Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _
To this:
对此：
.Execute "CREATE TABLE " & tblName & " ([BankName] text(50) WITH Compression, " & _
