running access parameter qry from excel - CROSSTAB RESULTS

[tuktuk]

using datapigs bacon bit i've found the following code (thanks by the way)

Sub RunParameterQuery()
 
 'cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
 
 
'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 _
("S:\Dept-Operational Analytics\BI\BI SECURE\DataWarehouse\Databases\IVR\IVR.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("qry_HighLevel_Crosstab_Monthly")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Source]") = Range("D3").Value
.Parameters("[Enter App]") = Range("D4").Value
End With
 
'Step 4: Open the query
Set Myrecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("Sheet2").Select
ActiveSheet.Range("A6:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset Myrecordset
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To Myrecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = Myrecordset.Fields(i - 1).Name
Next i
 
MsgBox "Your Query has been Run"
 
End Sub

this is working great by it is there a way to import the result of a CROSSTAB qry instead of a select.

here is my query

strSQL = "SELECT tbl_qry_IVR_Monthly_Summary.MonthYearMM, tbl_qry_IVR_Monthly_Summary.Source, tbl_qry_IVR_Monthly_Summary.[IVR App Name], IIf([CALL_END_REASON] Is Null,""zBlank"",[CALL_END_REASON]) AS Metric, Sum(tbl_qry_IVR_Monthly_Summary.SumOfCountOfDT2) AS SumOfSumOfCountOfDT2 " & vbCrLf & _
"FROM tbl_qry_IVR_Monthly_Summary " & vbCrLf & _
"WHERE (((tbl_qry_IVR_Monthly_Summary.Source)=[Enter Source] And (tbl_qry_IVR_Monthly_Summary.Source) Is Not Null) AND ((tbl_qry_IVR_Monthly_Summary.[IVR App Name])=[Enter App])) OR (((tbl_qry_IVR_Monthly_Summary.[IVR App Name])=[Enter App]) AND (([Enter Source]) Is Null)) OR (((tbl_qry_IVR_Monthly_Summary.Source)=[Enter Source]) AND (([Enter App]) Is Null)) OR ((([Enter Source]) Is Null) AND (([Enter App]) Is Null)) " & vbCrLf & _
"GROUP BY tbl_qry_IVR_Monthly_Summary.MonthYearMM, tbl_qry_IVR_Monthly_Summary.Source, tbl_qry_IVR_Monthly_Summary.[IVR App Name], IIf([CALL_END_REASON] Is Null,""zBlank"",[CALL_END_REASON]);"

i'm pretty lost on this one.

Thanks
Tuk

 

[xenou]

I don't see any problem - works fine for me. Query9 is a crosstab query with a parameter:

Sub RunParameterQuery()
 
 'cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
 
 
'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 _
("C:\myTemp\db1.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("Query9")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Agent ID]") = Range("A1").Value
End With
 
'Step 4: Open the query
Set Myrecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Range("A2:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
Range("A3").CopyFromRecordset Myrecordset
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To Myrecordset.Fields.Count
Cells(2, i).Value = Myrecordset.Fields(i - 1).Name
Next i
 
MsgBox "Your Query has been Run"
 
End Sub

I guess looking at your query SQL in your post one thing that doesn't make sense is that the query is not a crosstab (?).

 

[tuktuk]

cool...i got this to work. my quesiton is now if i want the values for my parameters to be selected form a list box, where the list box is linked to a query in the same database. I've found some code that is real close but running into issue.

one issue is...the code requires a ref to DAO 3.6 Object Lib....this is causing an issue since the above code we'v proved to work properly requires ActiveX data objuects.

here is the code to load the listboxes

Sub GetListboxData()
 Dim db As Database
 Dim rs As Recordset
 Dim dbName As String
 Dim query As String

 dbName = "S:MyPath\MyDatabase.accdb"

 'connect to access database
 Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

 query = "select distinct [IVR App Name] from tbl_IVR_APP_INFORMATION order by 1;"
 queryTWO = "select distinct [Source] from tbl_IVR_APP_INFORMATION order by 1;"
 'open cursor (start query)
 Set rs = db.OpenRecordset(query)

 'clear listbox to delete rows if present
 ListBox1.Clear
 
'getting recors
 Do While Not rs.EOF
 ListBox1.AddItem rs(0)
 rs.MoveNext
 Loop
 rs.Close
 db.Close
 Set rs = Nothing
 Set db = Nothing
 
 
 
  Set rs = db.OpenRecordset(queryTWO)

 'clear listbox to delete rows if present
 ListBox2.Clear
 
'getting recors
 Do While Not rs.EOF
 ListBox2.AddItem rs(0)
 rs.MoveNext
 Loop
 rs.Close
 db.Close
 Set rs = Nothing
 Set db = Nothing
 
End Sub

also once selected how do i refernce the value

.Parameters("[Enter Source]") = Listbox1.Value
.Parameters("[Enter App]") = Listbox2.Value