query a recordset
 

Hi

How do i query an open recordset. currently i have a query and then i have
another query that queries it.

i will post the query and the recordset SQL

thanks,

sam


this is the query:

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));



now i have this recordset in my VBA:


Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"

query a recordset
 

This is what I use

Private Sub QueryAccessDB()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate1 As String
Dim mydate2 As String
mydate1 = Sheets(1).Range("F1")
mydate2 = Sheets(1).Range("F2")
i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

'Use for jet
strSQL1 = "SELECT FIELDNAME, FIELDNAME2 " _
& "FROM TABLENAME " _
& "WHERE (((TIME_STAMP) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#)) " _
& "ORDER BY FIELDNAME; "

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!FIELDNAME1
'Sheets("Sheet1").Range("B" & i) = rs1!FIELDNAME2
'Sheets("Sheet1").Range("C" & i) = rs1!FIELDNAME3
'Sheets("Sheet1").Range("D" & i) = rs1!FIELDNAME4
'Sheets("Sheet1").Range("E" & i) = rs1!FIELDNAME5
'Sheets("Sheet1").Range("F" & i) = rs1!FIELDNAME6
'Sheets("Sheet1").Range("G" & i) = rs1!FIELDNAME7
'Sheets("Sheet1").Range("H" & i) = rs1!FIELDNAME8
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

"SAm" wrote:

Hi

How do i query an open recordset. currently i have a query and then i have
another query that queries it.

i will post the query and the recordset SQL

thanks,

sam


this is the query:

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));



now i have this recordset in my VBA:


Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"

query a recordset
 

first of all i would like to appologize for not posting this in the correct
category. this is an access question not an excel question (even though it
doesn't make much of a difference).

second, what you are posting is code to move through a recordset, and this
is not what i need. what i am looking for is to "query" a recordset.

thanks for your reply, but please help me find a solution.

sam

"Mike" wrote:

This is what I use

Private Sub QueryAccessDB()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate1 As String
Dim mydate2 As String
mydate1 = Sheets(1).Range("F1")
mydate2 = Sheets(1).Range("F2")
i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

'Use for jet
strSQL1 = "SELECT FIELDNAME, FIELDNAME2 " _
& "FROM TABLENAME " _
& "WHERE (((TIME_STAMP) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#)) " _
& "ORDER BY FIELDNAME; "

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!FIELDNAME1
'Sheets("Sheet1").Range("B" & i) = rs1!FIELDNAME2
'Sheets("Sheet1").Range("C" & i) = rs1!FIELDNAME3
'Sheets("Sheet1").Range("D" & i) = rs1!FIELDNAME4
'Sheets("Sheet1").Range("E" & i) = rs1!FIELDNAME5
'Sheets("Sheet1").Range("F" & i) = rs1!FIELDNAME6
'Sheets("Sheet1").Range("G" & i) = rs1!FIELDNAME7
'Sheets("Sheet1").Range("H" & i) = rs1!FIELDNAME8
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

"SAm" wrote:

Hi

How do i query an open recordset. currently i have a query and then i have
another query that queries it.

i will post the query and the recordset SQL

thanks,

sam


this is the query:

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));



now i have this recordset in my VBA:


Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"