Excel VBA Query Using Access Database Hangs on 2nd Execute

From: MikeK (MikeK_at_discussions.microsoft.com)
Date: 10/27/04

  • Next message: Chris Lambert: "How to Supress ADO Domian warning in IE" 
    Date: Wed, 27 Oct 2004 08:31:04 -0700

    I have a MS Access 2003 Database named "AS400 Fields.mdb". This
    database contains links to tables on an AS400.

    In MS Excel 2003, I have VBA code that creates and executes queries
    using the Access database, and returns the results to an Excel ***.
    There are many of these Excel query files and they all work at the
    same time on different users' machines.

    The first time the query is executed, results are returned to Excel in
    usually less than 10 seconds. However, if the query is executed a
    second time (without closing the Excel workbook), the code hangs at
    the line surrounded by "=",s in the code below (this was tested with
    message boxes and verifying that DB1 and RS1 were closed). If the Excel
    file containing the VBA code is closed
    and re-opened the code executes normally.

    Is there a problem in my code? Am I doing something wrong?

    Thanks,
    Mike

    Private Sub Execute_Query()
        Dim X As Integer
        Dim DB1 As ADODB.Connection
        Dim RS1 As ADODB.Recordset
        Const ConnectionStr As String =
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source= Myserver Location Goes here\AS400 Fields.mdb"
        Set DB1 = New ADODB.Connection
        DB1.Open ConnectionStr

        Set RS1 = New ADODB.Recordset
    ' NOTE: QryStr is a variable containing the SELECT statement
    '=============================
        RS1.Open QryStr, DB1
    '=============================
        'Copy the data
        ActiveCell.Offset(1, 0).Select
        ActiveCell.CopyFromRecordset RS1
        RS1.Close
        DB1.Close
        Set RS1 = Nothing
        Set DB1 = Nothing
    End Sub

  • Next message: Chris Lambert: "How to Supress ADO Domian warning in IE"