Excel vb Access query freezing

Posted on 2010-11-30
Last Modified: 2012-05-10

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

Question by:cbsbutler
  • 4
  • 3
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238094
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.

Author Comment

ID: 34238101
Hi rorya,

Yeah I tried that, but it still seems to freeze!
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238210
What is the backend database? Access, SQL Server or other?
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.


Author Comment

ID: 34238653
The database is access, the tables/queries in the database are linked tables from an SQL database.
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238664
Have you tried stepping through the code (using f8) to see where it freezes?

Expert Comment

ID: 34291284
Please step through the code to check where it hangs or write on error handler so as to check whats the error message

Accepted Solution

cbsbutler earned 0 total points
ID: 34291412
Apologies, it was actually an error with the Access Query.

Author Closing Comment

ID: 34328418

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now