Solved

Excel vb Access query freezing

Posted on 2010-11-30
8
375 Views
Last Modified: 2012-05-10
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
Comment
Question by:cbsbutler
  • 4
  • 3
8 Comments
 
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.
0
 

Author Comment

by:cbsbutler
ID: 34238101
Hi rorya,

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

Expert Comment

by:Rory Archibald
ID: 34238210
What is the backend database? Access, SQL Server or other?
0
 

Author Comment

by:cbsbutler
ID: 34238653
The database is access, the tables/queries in the database are linked tables from an SQL database.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Expert Comment

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

Expert Comment

by:manlimnag
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
0
 

Accepted Solution

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

Author Closing Comment

by:cbsbutler
ID: 34328418
Solved
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

706 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

18 Experts available now in Live!

Get 1:1 Help Now