Solved

Run queries using a macro

Posted on 2011-02-10
20
457 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

760 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

22 Experts available now in Live!

Get 1:1 Help Now