[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel vb Access query freezing

Posted on 2010-11-30
8
Medium Priority
?
389 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
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!

 

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

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!

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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