Dim myConn As ADODB.Connection

Question

我正在尝试两件事，其中一件是将excel工作簿与AS400连接，以获得系统中零件号的描述，但试图保持连接打开，或者至少在工作簿关闭之前。有人有什么建议吗？

Public Sub GetPartNumbers()
    Dim myConn As ADODB.Connection
    Dim myRS As ADODB.Recordset
''    Dim selVal As String
''    Dim selRow As Integer
    Set myConn = New ADODB.Connection
    myConn.ConnectionString = "Provider=SEQUEL ViewPoint;"
    myConn.Open
    Set myRS = New ADODB.Recordset

Answer 1

我就是这么做的。

Dim myConn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
On Error GoTo ErrorHandler
con.Open "PROVIDER=IBMDAS400;Data Source=999.999.999.999;USER ID= ;PASSWORD= ;"
Set myRs.ActiveConnection = myConn
End
Exit Sub
ErrorHandler:
MsgBox "Can not connect", vbInformation, cHeading
End
End Sub

Answer 2

我很同情你，因为在我了解VBA之前，我也必须弄清楚这一点。下面是一个简单的连接和循环结果示例。是的，你不应该使用Activate，但为了这个例子，我在中介绍了它

请注意，您应该清理最后的连接。如果使用错误处理程序，请确保在出现错误后退出之前关闭所有打开的连接。

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.Open "PROVIDER=IBMDAS400;Data Source=999.999.999.999;USER ID= ;PASSWORD= ;"
rst.ActiveConnection = cnn
rst.CursorLocation = adUseServer
'Query String (Specific to your database setup)
rst.Source = "SELECT DISTINCT F3002.IXLITM " _
                & "FROM WYATT.PRDDTA.F3002 F3002 " _
                & "WHERE (F3002.IXKITL='30P') AND (F3002.IXTBM='E')"
rst.Open
Worksheets("Sheet1").Range("A1").Activate
Do Until rst.EOF
    ActiveCell.Value = rst.Fields("IXLITM")
    rst.MoveNext
    ActiveCell.Offset(1, 0).Activate
Loop
'Clean up
rst.Close
Set rst = Nothing
Set cnn = Nothing