从Excel VBA创建SQL表

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

1 个解决方案

#1

Change this line:

改变这一行：

.Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _

To this:

对此：

.Execute "CREATE TABLE " & tblName & " ([BankName] text(50) WITH Compression, " & _