从Excel VBA创建SQL表

Asked
Viewd19323

6

问题是我无法获取输入到变量“ tblName”中的表名,而只能使用名为“ tblName”的表来正确命名数据库。

在用用户输入的名称创建名称后,是否可以通过某种方式在“ 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
 
  • 那您不应该接受它作为“答案”吗? ;)

    OorangMay 29, 2009 20:13
  • 总是很乐意提供帮助。另一只眼睛永远不会受伤。

    Mat NadrofskyMay 29, 2009 19:07
  • 谢谢,它的作用就像魅力。

    May 29, 2009 18:20

1 个答案

14

更改此行:

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

对此:

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