Solved

Excel vb Access query freezing

Posted on 2010-11-30
8
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:cbsbutler
ID: 34238653
The database is access, the tables/queries in the database are linked tables from an SQL database.
0
 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

719 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