Solved

Run queries using a macro

Posted on 2011-02-10
20
461 Views
Last Modified: 2012-05-11
I would like a macro or vba snippet that can execute the following actions in an Access db:

1. Run the following make table queries:
      1JerryToNMRemitDatav2
      q_CountyState
      q_StatePCPcounty
2. Run queries:
      M1Revenue1
      M1Revenue2_falloff
      MedicalCost1
      MedicalCost2
      Members1
     Members2

3. Open Excel and write the output of each of the above six queries consecutively on Sheet1.

Thanks !!
0
Comment
Question by:RishiSingh05
  • 10
  • 10
20 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34864415
You can't do all of this in a macro (particularly the Excel portion), but you can certainly create a function to do these tasks and run it from a macro, using a RunCode action.  You can run action queries (make-table, update, delete) this way.  What type are the queries in the 2nd group?

I like to give make-table queries the prefix "qmak" and the tables they create the same base name with the "tmak" prefix, so I know that (for example) qmakInvoice makes the table tmakInvoice.  Here is some code that will run a make-table query and import the table it creates to a new workbook:
Public Function RunQueriesAndExportToExcel()
'Created by Helen Feddema 10-Feb-2010
'Last modified by Helen Feddema 10-Feb-2010

On Error GoTo ErrorHandler
   
   Dim strWorkbook As String
   Dim strTable As String
   
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qmakCAContacts"
   strTable = "tmakCAContacts"
   strWorkbook = Application.CurrentProject.Path & "\" & "New Workbook.xls"
   DoCmd.TransferSpreadsheet transfertype:=acExport, _
      spreadsheettype:=acSpreadsheetTypeExcel8, _
      TableName:=strTable, _
      FileName:=strWorkbook, _
      hasfieldnames:=True
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in RunQueriesAndExportToExcel procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34864424
If the queries in the second group are select queries (with no parameters), they can also be exported to Excel using TransferSpreadsheet.
0
 

Author Comment

by:RishiSingh05
ID: 34864553
The six queries are all select queries.
0
 

Author Comment

by:RishiSingh05
ID: 34864603
Note that I don't need the tables (made by the make table queries) to go in Excel.
0
 

Author Comment

by:RishiSingh05
ID: 34864988
am I good to go then?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865016
Are the queries in the second group based on the output of the make-table queries?

If so, you can run the make-table queries, and then export each of the other queries to a separate workbook using TransferSpreadsheet.  If you want them to all be in the same workbook (perhaps on different sheets), that would take some VBA coding.  How exactly do you want to the data to appear in Excel?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865024
It might be best to prepare an Excel template, set up as you wish, with named sheets, and export the data to a workbook created from the template.
0
 

Author Comment

by:RishiSingh05
ID: 34865100
Yes the queries do need the tables created by the make table queries.  The entire output of each of the six queries in the second group need to go on the same worksheet of the same workbook.  If it is easier to put them in separate worksheets of the same workbook that will work too, but I would prefer them to be on the same sheet   .... one output after another without a blank row separating the outputs.  
0
 

Author Comment

by:RishiSingh05
ID: 34865253
I want to automate the process as we have more databases with queries to provide output to Excel.  Once I see how it's done I can do the others myself.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865265
I will work up some code and post it shortly.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:RishiSingh05
ID: 34865392
Thanks!!  No rush.  Tomorrow will do just as well.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865684
I tried using CopyFromRecordset using an Excel range, but it wouldn't work when run from Access VBA.  It does run from Excel VBA in a workbook.  Is that an acceptable alternative?  
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865721
This function should do it (with your query names):
Public Function RunQueriesAndExportToExcel()
'Created by Helen Feddema 10-Feb-2010
'Last modified by Helen Feddema 10-Feb-2010

On Error GoTo ErrorHandler
   
   Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   
   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"
   
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   
   'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
      
   'Export first query
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close
   
   'Export second query
   rst.Open Source:="qryTasks", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close
   
   'Export third query
   rst.Open Source:="qryAppointments", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in RunQueriesAndExportToExcel procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865736
As you can see, I did get it working from Access after all.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34865742
For some reason, DAO recordsets didn't work, but ADO recordsets did.
0
 

Author Comment

by:RishiSingh05
ID: 34871290
Thanks for your efforts.  I will look at the function and I may have some questions.  Thanks again.
0
 

Author Comment

by:RishiSingh05
ID: 34871399
Questions:

1) There are 3 make table queries I need to run.  

   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"    
' I will need to repeat the above command 3 times with my query names, correct?

2) Your code:
'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
' since I need to run 6 select queries, will I need to repeat the above block of code 5 more times (using my query names of course)?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 34872494
1.  Yes, add a line for each make-table query

2.  Same here -- just plug in the different query name each time.

0
 

Author Comment

by:RishiSingh05
ID: 34873163
thanks  ... here is what it now looks like. Pls give it a once over.  I will need to remove the double quote marks around the query names.


Public Function RunQueriesAndExportToExcel()
'Created by Helen Feddema 10-Feb-2010
'Last modified by Helen Feddema 10-Feb-2010


On Error GoTo ErrorHandler
   
   Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   
   'Run 3 make-table queries
   DoCmd.OpenQuery "1JerryToNMRemitDatav2"
   DoCmd.OpenQuery "q_CountyState"
   DoCmd.OpenQuery "q_StatePCPCounty"


   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   
   'Create a recordset based on a select query.
   rst.Open Source:=" m1Revenue1", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
      
   'Export query 1
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close
   
   'Export query 2
   rst.Open Source:="m1Revenue2_falloff", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close
   
   'Export query 3
   rst.Open Source:="MedicalCost1", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close

   'Export query 4
   rst.Open Source:="MedicalCost2", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close

'Export query 5
   rst.Open Source:="Members1", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close


   'Export query 6
   rst.Open Source:="Members2", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
   lngLastRow = sht.UsedRange.Rows.Count
   strRange = "A" & CStr(lngLastRow + 2)
   Debug.Print strRange
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close








   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in RunQueriesAndExportToExcel procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 

Author Comment

by:RishiSingh05
ID: 34874242
Also, I attempted to compile and got this error: “User-defined type not defined” for line:
Dim appExcel As New Excel.Application
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

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

13 Experts available now in Live!

Get 1:1 Help Now