代码之家 › 专栏 › 技术社区 › Sam Jo

如果InputBox等于Value1,则连接到数据库1

ssms excel vba

Sam Jo · 技术社区 · 6 年前

我正在尝试创建一个VBA代码,该代码将根据用户的输入连接到特定的数据库。E、 g.如果用户输入 DB1 在提示符中,代码将运行以下查询: 从MyServerName中选择*。DB1.dbo。桌子

以下是我目前掌握的情况:

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim strSQL As String
    Dim DB As Variant
    DB = InputBox("Please enter the Database Name.")

    sConnString = "Provider=SQLOLEDB;Data Source=MyServerName;" & _
                                   "Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Unsuccessful attempt to use the Else/If statement: 

    if '" & DB & "' = "DB1" then
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM MyServerName.DB1.dbo.Table ;")

    elseif '" & DB & "' = "DB2" then
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM MyServerName.DB2.dbo.Table ;")

    else
    MsgBox "No records returned. Enter the correct Database Name.", vbCritical
    End If

    If Not rs.EOF Then
    Sheets("Sheet1").Range("A2").CopyFromRecordset rs
    rs.Close
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing  

End Sub

当我在没有Else/If的情况下使用select语句时,连接本身工作正常。有人知道如何解决这个问题吗?非常感谢!