Run multiple Access Queries from Excel

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

KLM56Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
KLM56Author Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
KLM56Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
KLM56Author Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.