?
Solved

Run multiple Access Queries from Excel

Posted on 2012-09-12
6
Medium Priority
?
501 Views
Last Modified: 2012-09-13
Guys,

I have the code below that I use to run an Access query from Excel. Can you show me how I can alter this code to run addional queries. Each query will go on a separate tab in the spreedsheet. Also, only the first query will require dates parameter, but the others wont. Thanks for you help on this.

Private Sub CommandButton1_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("R:\StatsTracking.accde")
Set MyQueryDef = MyDatabase.QueryDefs("qryDailybyDate")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[[Forms]![frmReportsListBox]![txtStartDate]]") = txtStartDate
.Parameters("[[Forms]![frmReportsListBox]![txtBranchNo]]") = txtBranchNo
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("Daily Totals").Select
ActiveSheet.Range("A10:E17").ClearContents
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A10").CopyFromRecordset MyRecordset
 
'Step 7: Add column heading names to the spreadsheet
'For i = 1 To MyRecordset.Fields.count
'ActiveSheet.Cells(12, i).Value = MyRecordset.Fields(i - 1).Name
'Next i
 
'MsgBox "Your Query has been Run"
End Su

Open in new window

0
Comment
Question by:KLM56
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38392721
you can start another routine by changing the the querydef


Set MyQueryDef = MyDatabase.QueryDefs("anotherQuery")

Set MyRecordset = MyQueryDef.OpenRecordset

then do the selection of the Sheet as you did above..


to make your codes compact, you can place the names of the queries you want to run in array


dim qryArr(), j as integer
qryArr=array("q1","q2","q3")

for j=0 to ubound(qryArr)

Set MyQueryDef = MyDatabase.QueryDefs(qryArr(j))

Set MyRecordset = MyQueryDef.OpenRecordset

' routine codes to select worksheets and copy recordsets
next
0
 

Author Comment

by:KLM56
ID: 38392835
Thanks, I tried to make some of your suggestion.  Does anything I did make any sense. I first 2 queries require no parameters but the 3rd one does. Not sure how to tell it not to apply parameters to the queries. Also, did I do it right for the query to go on the correct sheets?

Private Sub CommandButton1_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
Dim qryArr(), j As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("R:\Tracking.accde")


qryArr = Array("qryHoeqDotInProcess", "qryMtgInProcess", "qryHoeqDotApproved")

For j = 0 To UBound(qryArr)
Set MyQueryDef = MyDatabase.QueryDefs(qryArr(j))
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[[Forms]![frmReportsListBox]![txtStartDate]]") = txtStartDate
.Parameters("[[Forms]![frmReportsListBox]![txtBranchNo]]") = txtBranchNo
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("HOEQ DOT IN PROCESS").Select
Sheets("MTG IN PROCESS").Select
Sheets("HOEQ DOT APPROVED").Select


'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A10").CopyFromRecordset MyRecordset
ActiveSheet.Range("A10").CopyFromRecordset MyRecordset
ActiveSheet.Range("A10").CopyFromRecordset MyRecordset

'MsgBox "Your Query has been Run"
End Sub

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38392949
try this



Private Sub CommandButton1_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
Dim qryArr(), j As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("R:\Tracking.accde")


qryArr = Array("qryHoeqDotInProcess", "qryMtgInProcess", "qryHoeqDotApproved")

For j = 0 To UBound(qryArr)
Set MyQueryDef = MyDatabase.QueryDefs(qryArr(j))
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 3: Define the Parameters

if qryArr(j)="qryHoeqDotApproved" then
	With MyQueryDef
	.Parameters("[[Forms]![frmReportsListBox]![txtStartDate]]") = txtStartDate
	.Parameters("[[Forms]![frmReportsListBox]![txtBranchNo]]") = txtBranchNo
	End With
 end if
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
'Step 6: Copy the recordset to Excel
		Select  case j
			case 0
				Sheets("HOEQ DOT IN PROCESS").Select
                                ActiveSheet.Range("A10:E17").ClearContents
				ActiveSheet.Range("A10").CopyFromRecordset MyRecordset

			Case 1
				Sheets("MTG IN PROCESS").Select
                                ActiveSheet.Range("A10:E17").ClearContents
				ActiveSheet.Range("A10").CopyFromRecordset MyRecordset

			Case 2 
				Sheets("HOEQ DOT APPROVED").Select
                                ActiveSheet.Range("A10:E17").ClearContents
				ActiveSheet.Range("A10").CopyFromRecordset MyRecordset


		End select




Next j
MsgBox "Your Query has been Run"
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:KLM56
ID: 38394753
Thank you so much. This works perfect when I run it from the Excel spreedsheet. What can I change so the user can enter the data on an Excel Userform. When I tried it on the userform, I get a compile error. "User-defined type not defined"  @ Dim MyDatabase As DAO.Database.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38394802
add to your references  Microsoft DAO x.x Object library  (x.x is the version number)

and if you have more question, please open a new thread.
0
 

Author Closing Comment

by:KLM56
ID: 38395574
Thank you.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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