Results 1 to 3 of 3
Thread: VBA Recordset querying
2009-09-03, 09:29 #1
- Join Date
- Apr 2001
- Warrington, Cheshire
- Thanked 0 Times in 0 Posts
Access 2007, office sp2
Having retrieved a recordset in vba, can I use that recordset against another table to do an update?
(sorry, insert code box not working for some reason, error on page)
Private Sub cmdUpdateValues_Click() 'need to create recordset to emulate the PO_13_Summary data so we can group it and sum the values per period Dim rst As DAO.Recordset Dim strSQL As String Dim strQuery As String Dim dbs As DAO.Database Dim intCount As Long strQuery = "qryTemp" Set dbs = CurrentDb Dim strClient As String Dim lngYear As Integer strClient = [Forms]![frmMain]![cmbClientRef].Value lngYear = [Forms]![frmMain]![cmbTxYr].Value strSQL = "SELECT dbo_vw_ClientRefs.Client_Ref, dbo_Valid_Timesheets.Purchase_Order, " & _ "dbo_Valid_Timesheets.Tax_Year, dbo_Valid_Timesheets.Tax_Period, " & _ "Sum((([Bill_Amount_1]+[bill_amount_2]+[bill_amount_3]+[bill_amount_4]+[bill _amount_5]+(IIf([Adhoc_1_Payment_TF]='T',1,-1)*[adhoc_bill_amount_1])+(IIf([Adhoc_2_Payment_TF]='T',1,-1)*[adhoc_bill_amount_2])+(IIf([Adhoc_3_Payment_TF]='T',1,-1)*[adhoc_bill_amount_3])))) AS BillAmount " & _ "FROM dbo_Valid_Timesheets INNER JOIN dbo_vw_ClientRefs ON " & _ "dbo_Valid_Timesheets.Client_Ref = dbo_vw_ClientRefs.Client_Ref " & _ "GROUP BY dbo_vw_ClientRefs.Client_Ref, dbo_Valid_Timesheets.Purchase_Order, " & _ "dbo_Valid_Timesheets.Tax_Year, dbo_Valid_Timesheets.Tax_Period, dbo_Valid_Timesheets.Client_Ref " & _ "HAVING (((dbo_Valid_Timesheets.Tax_Year)=" & lngYear & ") " & _ "AND ((Sum((([Bill_Amount_1]+[bill_amount_2]+[bill_amount_3]+[bill_amount_4]+[bill_am ount_5]+(IIf([Adhoc_1_Payment_TF]='T',1,-1)*[adhoc_bill_amount_1])+(IIf([Adhoc_2_Payment_TF]='T',1,-1)*[adhoc_bill_amount_2])+(IIf([Adhoc_3_Payment_TF]='T',1,-1)*[adhoc_bill_amount_3])))))<>0) " & _ "AND ((dbo_Valid_Timesheets.Client_Ref)='" & strClient & "'));" Set qdf = dbs.CreateQueryDef(strQuery, strSQL) Set rst = dbs.OpenRecordset(strQuery) With rst .MoveFirst .MoveLast intCount = .RecordCount End With
creates and counts the recordset contents, but can I use that recordset in another subsequent query to perform the update?
If there is recommended reading I can follow that, but pointers would be most welcome.
Cheshire, sunny and windy.
2009-09-03, 18:31 #2
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 64 Times in 63 Posts
Once your QueryDef has been created, then it is available to the database engine as a regular query and you can do joins and other things just as you could with a query you created manually. However it is probable that any query you create using it will not be updateable, as the query is doing Group By things, and in addition it appears you have some SQL Server views as data sources for the query you are creating. Views connected to from Access are generally not updateable unless you specifically create them with unique indexes - that topic goes beyond the depth of this forum unfortunately. For this sort of thing I would suggest either creating a temporary table with the appropriate primary key, or actually creating the temporary table in SQL Server. I'll see if I can find a book that gets into this sort of detail.
Well not much luck in finding an intermediate level book that talks about indexed views and schema binding. Any of the indepth books you can find will probably have something, but it would be a function of the version you are linking to. And I think John's suggestion is probably the best if it isn't too slow - looping through the recordsets in VBA would be something you can do withour digging into SQL Server.Wendell
2009-09-04, 04:17 #3
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
If you have opened a recordset, and want to use the contents to update values in another table, the way I would do that would be to open a second recordset based on the table I want to update.
For each record in the first recordset, find the relevant record in the second recordset, set values using values from the first recordset, update, then move to the next record of the first recordset. Continue until all records of the first recordset have been processed.Regards