从Excel VBA创建SQL表 [英] Create a SQL Table from Excel VBA

问题描述

问题是我无法获取输入变量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 
 
'在Excel工作表中设置数据库名称
 dbPath = ActiveSheet.Range（B1 ）.Value'数据库名称
 tblName = ActiveSheet.Range（B2）。值'表名称
 
 dbConnectStr =Provider = Microsoft.Jet.OLEDB.4.0; Data Source = &安培; dbPath& ; 
 
'使用Excel Cell（B1）中输入的名称创建新数据库
 Set Catalog = CreateObject（ADOX.Catalog）
 Catalog.Create dbConnectStr 
 Set Catalog = Nothing 
 
'连接到数据库并插入新表
设置cnt =新建ADODB.Connection 
带有cnt 
 .Open dbConnectStr 
。执行CREATE TABLE tblName（[BankName] text（50）WITH Compression，& _ 
[RTNumber] text（9）WITH Compression，& _ 
[AccountNumber] ）WITH COMPression& _ 
[Address] text（150）WITH Compression，& _ 
[City] text（50）WITH Compression，& _ 
 [StateState] text（2）WITH Compression，& _ 
[Postal] text（6）WITH Compression，& _ 
[AccountAmount] decimal（6））
结束
设置cnt =没有
 
结束Sub 
  

解决方案

更改此行：

  .ExecuteCREATE TABLE tblName（[BankName] text（50）WITH Compression，& _ 
  

为此：

 。执行CREATE TABLE& tblName& （[BankName] text（50）WITH Compression，& _ 
  

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".

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?

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

解决方案

Change this line:

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

To this:

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

这篇关于从Excel VBA创建SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！