Recordset Theory

[GBalcom]

I have an instance where I have 3 recordsets I need open at one time.

Let's say Query A, then table A and Table B that I'm populating. So, Recordset #1 loops through qryA, then based on a Logical test, I need to either enter records into tblA (rs2) or tblB (rs3), depending on the logical test.

is there any reason I can't just call a function for the rs2 or rs3 depending on the result?

This will close and reopen each recordset a few times, but does it matter?

thanks!

 

[spikepl]

Do it and see. There is not enough information to make a recommendation.

In general looping recordsets with VBA is relatively slow compared to making queries do the same job, becasue qeuries take advange of indexing and optimisation.

 

[vbaInet]

Perhaps you explain the logical tests we can advise how you can translate that into a query as spikepl advised.

 

[MarkK]

I need to either enter records into tblA (rs2) or tblB (rs3)

If you are doing inserts or updates in these operations, you could use action (UPDATE or INSERT) queries, rather than recordsets.

 

[jdraw]

Can you provide more info specific to your situation?
What are the tables and query involved? The logic tests?
As others have said, you might be able to use SQL action queries instead of recordsets.

Seems it would be something you could mock up and test - even if only oing Debug.Print of what would happen without actually affecting table data.

 

[GBalcom]

Good Morning Gentlemen,
Sorry for the delay, but I'm not a full time programmer, and my other duties pulled me a way for a bit.

I'll attach what I've come up with so far, which seems to be working. Basically, I put together a recordset for the query I'm browsing, and a recordset to populate tblA. This values pushed to this table have some calculations going on, and with my limited experience, it was easier to do this in a recordset. When I populate tblB, I was able to do that with an SQL INSERT statement.

Opinions appreciated

Private Sub CmdRun_Click()
    '====================================================================
    ' CommenCreateDate: This is the "On" switch for this application
    ' State of Code: Under Development
    ' Params  :
    ' Returns :
    ' Created : 05/29/2015 01:15 PM GB
    ' Modified:
    '====================================================================
    
    'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd

'Query new alerts
    'check last date in table log
    Dim LastDate As Date
        LastDate = GetLastDate()
    'MsgBox LastDate
    
    'Change Query parameter to match last date
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryAlertsGenTgtFinishDt")

    'Set the value of the QueryDef's parameter
    qdf.Parameters("LastDate").Value = LastDate


    'delete contenCreateDate of the tblCalendarEvenCreateDateInsert table to ensure we have a clean slate
    Dim strSQL As String
        strSQL = "DELETE * FROM tblCalendarEventsInsert"
    
    CurrentDb.Execute strSQL
    

    
    'Open the query in a recordset
    
   
    Dim rst As DAO.Recordset    'this is the alerCreateDate query
    Dim rstTbl As DAO.Recordset 'this is the tblCalenderEvenCreateDateInsert
    Dim JobNbr As Long
    Dim WoNbr As Long
    Dim tgtFinishDate As Date
    Dim ColorID As Integer  'If xxx, then we use color #11, else it's 9
    Dim RecNum  As Integer

    Set dbs = CurrentDb
    Set rst = qdf.OpenRecordset
    Set rstTbl = dbs.OpenRecordset("tblCalendarEventsInsert", dbOpenDynaset, dbSeeChanges)
    RecNum = 0

    With rst
        Do While Not .EOF
               'populate variables
                JobNbr = .Fields("JobNbr").Value
                WoNbr = .Fields("WoNbr").Value
                tgtFinishDate = fFindWoTblInfo(WoNbr, "TargetFinDt")
                
                'calendar color
                If fFindJobTblInfo(JobNbr, "CompanyNbr") = 8 Then
                    ColorID = 11    'Red for LES
                Else
                    ColorID = 9     'Blue for else
                End If
                  
                'Maintain Record Count
                RecNum = RecNum + 1
                
                'Check to see if this event is new
                If IsNewEvent(WoNbr) Then
                    'proceed to enter new event
                         rstTbl.AddNew
                                                      
                            rstTbl![CalendarId] = "schedule@xxxxxxx.com"
                            rstTbl![Summary] = fBuildSummary(WoNbr)
                            rstTbl![Description] = fBuildDescription(WoNbr)
                            rstTbl![AllDayEvent] = True
                            rstTbl![StartDateTime] = tgtFinishDate
                            rstTbl![EndDateTime] = tgtFinishDate + 1
                            rstTbl![ColorID] = ColorID
                           
                    rstTbl.Update
                Else
                    'Pull EventId out of the tblLog
                    Dim EventId As String
                        EventId = DLookup("CalendarEventId", "tblLog", "WorkOrderID ='" & WoNbr & "'")
                    If fUpdateEventDate(EventId, tgtFinishDate) Then
                        Debug.Print "success"
                    Else
                        Debug.Print "failed"
                    End If
                    
                    
    
                    
                End If

            .MoveNext
        Loop
        .Close
        rstTbl.Close
    End With
    Set rst = Nothing
    Set rstTbl = Nothing
    dbs.Close
    Set dbs = Nothing
    
    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing

Public Function fUpdateEventDate(EventId As String, tgtFinishDate As Date) As Boolean
    '====================================================================
    ' Comments:
    ' State of Code: Working
    ' Params  :
    ' Returns : Boolean
    ' Created : 06/02/2015 11:15 AM GB
    ' Modified:
    '====================================================================
    'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd

Dim strSQL As String
Dim StartDate As Date
Dim EndDate As Date

StartDate = tgtFinishDate
EndDate = tgtFinishDate + 1

    strSQL = "UPDATE [GoogleApps_CalendarEvents] " & _
             "SET [StartDateTime] = #" & StartDate & "#, " & _
             "[EndDateTime] = #" & EndDate & "# " & _
             "WHERE [Id] = '" & EventId & "'"
             
     Debug.Print strSQL
     
     CurrentDb.Execute strSQL
     

'insert changedate in tblLog
        strSQL = "UPDATE [tblLog] " & _
             "SET [ChangedDate] = #" & Now() & "#" & _
             "WHERE [Id] = '" & EventId & "'"
    
    
    
    fUpdateEventDate = True

    'ErrorHandlerStart
EXIT_PROCEDURE:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mGoogleApps.fUpdateEventDate"
    Resume EXIT_PROCEDURE
    'ErrorHandlerEnd

End Function

I know I'll have at least one more question for the experts on this forum, but I need to work through it myself first so I can verbalize it.

Thanks for looking!

 

[vbaInet]

I know I'll have at least one more question for the experts on this forum, but I need to work through it myself first so I can verbalize it.

You guessed right.

It's best you verbalise it first then we can advise.

NB: By the way, was it you who was trying to update the Google calendar in one of your threads? I think I replied to that thread.

 

[GBalcom]

VBAInet,
Yes, that was me. I'm happy to say I've been able to accomplish that with the above code. I'm using a ODBC Driver for google apps from Cdata, which seems to be working nicely. There is a limitation with column widths (254 I believe), but I don't think this will affect me.

Thanks to all for the help thus far

 

[The_Doc_Man]

If your test for choosing between table A and table B is ABSOLUTELY dichotomous and you NEVER have a case where you must insert into both tables or into neither table, I might try this

test = "(FIELD1 > x) AND (FIELD2 LIKE '*y*') AND (FIELD3 <> 'z')"  (or whatever your test REALLY is...)

qryInsA = "INSERT INTO tblA (field1, field2, ..., fieldn) FROM (SELECT field1, field2, ..., fieldn) FROM qryA WHERE (" & test & ") ); "

qryInsB = "INSERT INTO tblB(field1, field2, ..., fieldn) FROM (SELECT field1, field2, ..., fieldn) FROM qryA WHERE NOT (" & test & ") ); "

docmd.runsql qryInsA
docmd.runsql qryInsB

If the order of insertion is important (but in Access it never is), you can include an ORDER BY in the "FROM SELECT ...." sequences. The better choice would be that one of the table's fields is a PK or will become a PK or will otherwise have an index and you can use that to make the data come OUT of the tables in the right order.

You could, in theory, step through the qryA recordset one record at a time to test the conditions, but this ought to be faster. Note also that if the test cannot be absolute, your recordset method and the two-complementary-queries method would be exposed to the same error. It is just that it would be easier to handle in the recordset method.

 

[vbaInet]

You still haven't verbalised what 'logical tests' you're doing? Your code may not be a precise representation of what you're actually trying to accomplish that's why we need an explanation of what the tests are.