[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Excel vb Access query freezing


I've attached some vb code that queries from an access database and copies the results to a new worksheet named after the access query.

The queries require user input parameters.

I want to add another query to the list, this query does not require any parameters.
However since adding this new query, the Sub freezes.

I'm at a loss as to why it's freezing.
Sub RunAccessQueries()
    Dim cnn As ADODB.Connection
    Dim strQuery As String
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim prm As ADODB.Parameter, prms As ADODB.Parameters
    Dim strPathToDB As String
    Dim wks As Worksheet
    Dim i As Long
    Dim varSheetNames, varSheetName
    varSheetNames = Array("Consultant list", "Interviews and offers", "Current contractors", "Permanent placements")
    ' change database path and query name as required
    strPathToDB = ThisWorkbook.Sheets("Create Report").Range("J3").Value

    ' open database connection
    Set cnn = New ADODB.Connection
    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=" & strPathToDB & ";"
    End With
    ' loop through report list
    For Each varSheetName In varSheetNames
        Set wks = Sheets.Add
        wks.Name = varSheetName
        ThisWorkbook.Sheets(varSheetName).Visible = False ' Hide report sheet
        strQuery = "[" & varSheetName & "]"
        Set cmd = New ADODB.Command
        With cmd
            Set .ActiveConnection = cnn
            .CommandText = strQuery
            .CommandType = adCmdTable
            ' Change parameter names as necessary
            If varSheetName = "Current contractors" Then
                .Parameters("[Enter earliest contract end date]").Value = Sheets("Template").Range("C13").Value
                .Parameters("[Enter latest contract start date]").Value = Sheets("Template").Range("C13").Value
            ElseIf varSheetName = "Consultant list" Then
                .Parameters("[Enter commencing date]").Value = Sheets("Template").Range("F7").Value
                .Parameters("[Enter ending date]").Value = Sheets("Template").Range("H7").Value
            End If
        End With
        Set rst = New ADODB.Recordset
        rst.Open cmd
        With rst
           If Not (.EOF And .BOF) Then
              'Populate field names
              For i = 1 To .Fields.Count
                 wks.Cells(1, i) = .Fields(i - 1).Name
              Next i
              ' Copy data
              wks.Range("A1").CopyFromRecordset rst
           End If
        End With
        Set rst = Nothing
        Set cmd = Nothing

    Next varSheetName
    Set cnn = Nothing
End Sub

Open in new window

  • 4
  • 3
1 Solution
Rory ArchibaldCommented:
Have you tried moving the Parameters.Refresh bit so that it is inside both of the two If...Then bits for the two parameter queries? Doesn't seem much point calling it for a non-parameter query.
cbsbutlerAuthor Commented:
Hi rorya,

Yeah I tried that, but it still seems to freeze!
Rory ArchibaldCommented:
What is the backend database? Access, SQL Server or other?
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

cbsbutlerAuthor Commented:
The database is access, the tables/queries in the database are linked tables from an SQL database.
Rory ArchibaldCommented:
Have you tried stepping through the code (using f8) to see where it freezes?
Please step through the code to check where it hangs or write on error handler so as to check whats the error message
cbsbutlerAuthor Commented:
Apologies, it was actually an error with the Access Query.
cbsbutlerAuthor Commented:

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now