Solved

Excel vb Access query freezing

Posted on 2010-11-30
8
376 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

932 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

12 Experts available now in Live!

Get 1:1 Help Now