Building Records in Access

[CopperWire]

In Excel, I use an index to build a record set that I need to import into an oracle table and I can't do that anymore as my record set is now to large for Excel to handle. Can I do something similiar in Access?

What I need to do is look at a set of records for a specific criteria and if that record set doesn't have 30 records, then I need to build the missing ones. I have to have exactly 30 even if the $ values are zero.

Any help would be appreciated. Thanks!

 

[Elysium]

CopperWire,

This should do the trick. I setup an environment with the following:

Test <-- table
Row <-- field

Table Test has 15 rows for arguments sake and 6 of those rows have the value of 1. The code queries the table for all rows that equal 1. Since only 6 are returned, it adds another 24 empty rows to the recordset. From here, you can add the contents of the recordset to your oracle table, but I didn't include the code to do so. If you need it, let me know.

Private Sub TestForRows()
Dim r As Integer
Dim oRS As New ADODB.Recordset

    Set oRS = ThirtyRows
    
    r = 1
    oRS.MoveFirst
    Do Until oRS.EOF
        MsgBox r & ": " & oRS(0)
        r = r + 1
        oRS.MoveNext
    Loop
    
Set oRS = Nothing

End Sub

Private Function ThirtyRows() As ADODB.Recordset
Dim rs30 As New ADODB.Recordset
Dim sSQL As String
Dim r As Integer

    sSQL = "SELECT * FROM TEST WHERE ROW=1"
    rs30.CursorLocation = adUseClient
    rs30.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
    
    If rs30.RecordCount < 30 Then
        For r = rs30.RecordCount + 1 To 30
            With rs30
                .AddNew
                rs30(0) = Null
                .Update
            End With
        Next r
    End If
    
    Set ThirtyRows = rs30

Set rs30 = Nothing

End Function

Let me know how it works out.

Randy