Sub getSQLdata()
Const L_REC_PER_WS& =  65535 
Dim rs
Dim scn$, sq$
Dim k&, i&, t!

Application.ScreenUpdating =  0 
t = Timer
Debug.Print "timer start: " & t

scn = "Provider=MSDAORA;Server=127.0.0.1;Data Source=XXX;user id=YYY;password=ZZZ;"
sq = "select count(*) from JOURNAL2 where JDATE between '01-JAN-2007' and '31-DEC-2010'"
Set rs = CreateObject("adodb.recordset")
rs.Open sq, scn,  3 ,  3 ,  1 
k = rs( 0 ).Value
rs.Close

i = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = k \ L_REC_PER_WS +  1 

Debug.Print "records: " & k
sq = "select JDATE,JTIME,EVENT_TYPE,RID,SERTIF from JOURNAL2 where JDATE between '01-JAN-2007' and '31-DEC-2010'"
rs.Open sq, ,  0 ,  1 ,  1 

With Workbooks.Add
Application.SheetsInNewWorkbook = i
    For i =  1  To .Worksheets.Count
        With .Worksheets(i)
            .[a1].CopyFromRecordset rs, L_REC_PER_WS
        End With
        Debug.Print "timer ws(" & i & "): " & Timer - t
    Next i
End With

rs.Close
rs.activeconnection.Close
Set rs = Nothing

Debug.Print "timer stop: " & Timer - t
Application.ScreenUpdating = - 1 
End Sub

timer start:  72358 , 28 
records:  943175 
timer ws( 1 ):  6 , 96875 
timer ws( 2 ):  8 , 640625 
timer ws( 3 ):  10 , 26563 
timer ws( 4 ):  11 , 95313 
timer ws( 5 ):  13 , 59375 
timer ws( 6 ):  15 , 21875 
timer ws( 7 ):  16 , 9375 
timer ws( 8 ):  18 , 54688 
timer ws( 9 ):  20 , 14063 
timer ws( 10 ):  21 , 85938 
timer ws( 11 ):  23 , 5625 
timer ws( 12 ):  25 , 5 
timer ws( 13 ):  29 , 79688 
timer ws( 14 ):  48 , 09375 
timer ws( 15 ):  63 , 45313 
timer stop:  63 , 46875 

Sub getSQLdata2()
Const L_REC_PER_WS& =  65535 
Dim rs
Dim scn$, sq$
Dim i&, t!

Application.ScreenUpdating =  0 
t = Timer
Debug.Print "timer start: " & t

scn = "Provider=MSDAORA;Server=127.0.0.1;Data Source=XXX;user id=YYY;password=ZZZ;"
sq = "select JDATE,JTIME,EVENT_TYPE,RID,SERTIF from JOURNAL2 where JDATE between '01-JAN-2007' and '31-DEC-2010'"
Set rs = CreateObject("adodb.recordset")
rs.Open sq, scn,  0 ,  1 ,  1 

With Workbooks.Add
    i =  0 
    Do Until rs.EOF
       i = i +  1 
        If .Worksheets.Count < i Then
            With .Worksheets.Add(after:=.Worksheets(i -  1 ))
                .[a1].CopyFromRecordset rs, L_REC_PER_WS
            End With
        Else
            .Worksheets(i).[a1].CopyFromRecordset rs, L_REC_PER_WS
        End If
        Debug.Print "timer ws(" & i & "): " & Timer - t
    Loop
    Debug.Print "records: " & L_REC_PER_WS * (i -  1 ) + .Worksheets(i).UsedRange.Rows.Count
End With

rs.Close
rs.activeconnection.Close
Set rs = Nothing

Debug.Print "timer stop: " & Timer - t
Application.ScreenUpdating = - 1 
End Sub

timer start:  73500 , 64 
timer ws( 1 ):  6 , 4375 
timer ws( 2 ):  8 , 21875 
timer ws( 3 ):  9 , 75 
timer ws( 4 ):  11 , 4375 
timer ws( 5 ):  13 , 09375  
timer ws( 6 ):  14 , 75 
timer ws( 7 ):  16 , 5625 
timer ws( 8 ):  18 , 64063 
timer ws( 9 ):  20 , 98438 
timer ws( 10 ):  23 , 39063 
timer ws( 11 ):  25 , 60938 
timer ws( 12 ):  28 , 39063 
timer ws( 13 ):  33 , 42188 
timer ws( 14 ):  51 , 04688 
timer ws( 15 ):  66 , 25 
records:  943175 
timer stop:  66 , 26563