[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Excel vb Access query freezing

Morning,

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 & ";"
       .Open
    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
            .Parameters.Refresh
            
            ' 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
            Else
                .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
        rst.Close
        Set rst = Nothing
        Set cmd = Nothing

    Next varSheetName
    cnn.Close
    Set cnn = Nothing
    
End Sub

Open in new window

0
cbsbutler
Asked:
cbsbutler
  • 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.
0
 
cbsbutlerAuthor Commented:
Hi rorya,

Yeah I tried that, but it still seems to freeze!
0
 
Rory ArchibaldCommented:
What is the backend database? Access, SQL Server or other?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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