Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: VBA SQL query on query/multiple recordsets

    Long time no speak everyone!

    I've been rattling along quite nicely with my VBA learning curve but have hit the next obstacle which I've failed to find the solution to.

    Step 1 is to create and query a recordset and then requery THAT recordset to give me a new recordset. I've achieved this as a query on query in the Design window until now. The intention is to give me an end recordset 'a'

    A similar thing happens with different tables to give me an end recordset 'b'

    recordset 'a' and recordset 'b' are then combined and queried upon and the results sent to a new table.

    Googling has turned up that opening one recordset closes another- is that true? I've tried different named recordsets as you can see. Nested SQL statements have also been mentioned but without any idea given as to how to go about it. My futile shot in the fading light so far:

    Code:
    Public Sub Attended()
    
    Dim MyConnection As ADODB.Connection
    Set MyConnection = CurrentProject.Connection
    Dim MyRecordsetA1 As New ADODB.Recordset
    MyRecordsetA1.ActiveConnection = MyConnection
    Dim MyRecordsetA2 As New ADODB.Recordset
    MyRecordsetA2.ActiveConnection = MyConnection
    
    Dim MySQLA1 As String
    
    MySQLA1 = "SELECT tblEmployeeCourse.[Employee#], tblCourseOccurrence.CourseName, tblCourseOccurrence.CourseDate, "
    MySQLA1 = MySQLA1 + "lutblEmployeeCourseStatus.EmployeeCourseStatusCode , tblEmployeeCourse.EmployeeCourseStatus, "
    MySQLA1 = MySQLA1 + "tblEmployeeCourse.OverrideRebook FROM tblEmployee INNER JOIN "
    MySQLA1 = MySQLA1 + "(tblCourseOccurrence INNER JOIN (lutblEmployeeCourseStatus INNER JOIN tblEmployeeCourse "
    MySQLA1 = MySQLA1 + "ON lutblEmployeeCourseStatus.EmployeeCourseStatus = tblEmployeeCourse.EmployeeCourseStatus) "
    MySQLA1 = MySQLA1 + "ON tblCourseOccurrence.[CourseOccurrence#] = tblEmployeeCourse.[CourseOccurrence#]) ON "
    MySQLA1 = MySQLA1 + "tblEmployee.[Employee#] = tblEmployeeCourse.[Employee#] "
    MySQLA1 = MySQLA1 + "WHERE (((tblCourseOccurrence.CourseDate)<Date()) "
    MySQLA1 = MySQLA1 + "AND ((lutblEmployeeCourseStatus.EmployeeCourseStatusCode)='05' Or (lutblEmployeeCourseStatus.EmployeeCourseStatusCode)='09') "
    MySQLA1 = MySQLA1 + "AND ((tblEmployeeCourse.OverrideRebook)='No' Or (tblEmployeeCourse.OverrideRebook) Is Null))"
    
    MyRecordsetA1.Open MySQLA1
    
    Dim MySQLA2 As String
    
    MySQLA2 = "SELECT MySQLA1.[Employee#], MySQLA1.CourseName, Max(MySQLA1.CourseDate) "
    MySQLA2 = MySQLA2 + "AS MaxOfCourseDate, MySQLA1.EmployeeCourseStatusCode, MySQLA1.EmployeeCourseStatus FROM MySQLA1 "
    MySQLA2 = MySQLA2 + "GROUP BY MySQLA1.[Employee#], MySQLA1.CourseName, MySQLA1.EmployeeCourseStatusCode, MySQLA1.EmployeeCourseStatus"
    
    MyRecordsetA2.Open MySQLA2
    MyRecordsetA1.Close
    
    Dim MySQLA3 As String
    
    MySQLA3 = "SELECT MySQLA2.[Employee#], MySQLA2.CourseName, MySQLA2.MaxOfCourseDate, lutblCourseName.CourseNameExpired, "
    MySQLA3 = MySQLA3 + "lutblCourseName.CourseDueAttendanceInWeeks, [MaxOfCourseDate]+([CourseDueAttendanceInWeeks]*7) AS DueAttendanceDate "
    MySQLA3 = MySQLA3 + "FROM MySQLA2 INNER JOIN lutblCourseName ON MySQLA2.CourseName = lutblCourseName.CourseName"
    
    MyRecordsetA1.Open MySQLA3
    
    'Close the first recordset in the procedure
    
    MyRecordsetA2.Close
    
    'Combine the Future course date recordset with the Attended recordset.
    
    Call FutureCourseDate
    
    Dim MySQLA4 As String
    
    MySQLA4 = "INSERT INTO mdtblEmployeeRenewCourse ( PriorityByDate, [Employee#], CourseName, CourseDate, FlagAs ) "
    MySQLA4 = MySQLA4 + "SELECT MySQLA3.DueAttendanceDate, MySQLA3.[Employee#], MySQLA3.CourseName, MySQLA3.MaxOfCourseDate, 'Renew Training' AS FlagAs "
    MySQLA4 = MySQLA4 + "FROM MySQLA3 LEFT JOIN MySQLFCD2 ON (MySQLA3.[Employee#] = MySQLFCD2.[Employee#]) "
    MySQLA4 = MySQLA4 + "AND (MySQLA3.CourseName = MySQLFCD2.CourseName) WHERE (((MySQLA3.DueAttendanceDate)<Date() "
    MySQLA4 = MySQLA4 + "Or (MySQLA3.DueAttendanceDate) Between Date()-14 And Date()) "
    MySQLA4 = MySQLA4 + "AND ((MySQLFCD2.[Employee#]) Is Null) AND ((MySQLFCD2.CourseName) Is Null)) "
    MySQLA4 = MySQLA4 + "ORDER BY MySQLA3.DueAttendanceDate, MySQLA3.[Employee#], MySQLA3.CourseName"
    
    DoCmd.RunSQL MySQLA4
    
    'Close open recordsets in the procedure
    
    MyRecordsetA1.Close
    MyRecordsetFCD2.Close
    
    'Terminate all recordsets in the module
    
    Set MyRecordsetA1 = Nothing
    Set MyRecordsetA2 = Nothing
    Set MyRecordsetFCD2 = Nothing
    
    Set MyConnection = Nothing
    
    End Sub
    
    Public Sub FutureCourseDate()
    
    'Connection etc defined in other procedures that call this procedure.
    
    Dim MySQLFCD1 As String
    
    MySQLFCD1 = "SELECT tblEmployeeCourse.[Employee#], tblCourseOccurrence.CourseName, tblCourseOccurrence.CourseDate, lutblCourseName.CourseNameExpired, "
    MySQLFCD1 = MySQLFCD1 + "tblCourseOccurrence.CourseOccurrenceStatus, lutblEmployeeCourseStatus.EmployeeCourseStatusCode "
    MySQLFCD1 = MySQLFCD1 + "FROM tblEmployee INNER JOIN ((lutblCourseName INNER JOIN tblCourseOccurrence "
    MySQLFCD1 = MySQLFCD1 + "ON lutblCourseName.CourseName = tblCourseOccurrence.CourseName) INNER JOIN (lutblEmployeeCourseStatus "
    MySQLFCD1 = MySQLFCD1 + "INNER JOIN tblEmployeeCourse ON lutblEmployeeCourseStatus.EmployeeCourseStatus = tblEmployeeCourse.EmployeeCourseStatus) "
    MySQLFCD1 = MySQLFCD1 + "ON tblCourseOccurrence.[CourseOccurrence#] = tblEmployeeCourse.[CourseOccurrence#]) "
    MySQLFCD1 = MySQLFCD1 + "ON tblEmployee.[Employee#] = tblEmployeeCourse.[Employee#] WHERE (((tblCourseOccurrence.CourseDate)>Date()) "
    MySQLFCD1 = MySQLFCD1 + "AND ((lutblCourseName.CourseNameExpired)=False) AND ((tblCourseOccurrence.CourseOccurrenceStatus)='01') "
    MySQLFCD1 = MySQLFCD1 + "AND ((lutblEmployeeCourseStatus.EmployeeCourseStatusCode)='01'))"
    
    MyRecordsetFCD1.Open MySQLFCD1
    
    Dim MySQLFCD2 As String
    
    MySQLFCD2 = "SELECT MySQLFCD1.[Employee#], MySQLFCD1.CourseName, Min(MySQLFCD1.CourseDate) AS MinOfCourseDate"
    MySQLFCD2 = MySQLFCD2 + "FROM MySQLFCD1 GROUP BY MySQLFCD1.[Employee#], MySQLFCD1.CourseName"
    
    'Recordset remains open to be used and closed by the procedure calling it.
    
    MyRecordsetFCD2.Open MySQLFCD2
    
    'Close the first recordset
    
    MyRecordsetFCD1.Close
    
    'Terminate the first recordset
    
    Set MyRecordsetFCD1 = Nothing
    
    
    End Sub
    Failing any solution I'll either leave the queries intact in the design window or send the interim results to temporary tables but it would be nice to do it 'properly'! Thanks for any assistance or something to get me heading in the right direction- I realise I could be way off the mark!

  2. #2
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Maybe there's too much to take on board there so I'll chop it down to a few questions.

    Is it possible to open multiple recordsets? I've seen conflicting advice. If so, am I correct in defining seperate recordset names? It's surprisingly difficult to find any clear advice or examples. Has anyone got a good source of help on this please?

    Thanks

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jonesyfella
    recordset 'a' and recordset 'b' are then combined and queried upon and the results sent to a new table.
    Code:
    select stuff from a and/or b
      into anewtable
      from ( select stuff
               from whatever ) as a
    inner
      join ( select stuff
               from whatever ) as b
        on b.somecols = a.somecols
    i don't understand from "recordsets" but that's probably because i'm not a programmer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Thanks Rudy. Recordsets are virtual tables and don't behave the same unfortunately from what I can gather. It's taken me all day to get a small recordset to output to Excel which is the next step- I wouldn't have thought it was that hard!

Posting Permissions

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