Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    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, " & _
    _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
    		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.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 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.

  3. #3
    Super Moderator
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts