Pass recordset info to a table?

[Guest]

How can you do this in Access VBA code?

 

[Arvin Meyer [MVP]]

You should use an Append query because it is faster, but you can use the
addnew/update method:

Public Sub MoveRecords()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstDestination As DAO.Recordset
Dim fld As Field

Set db = CurrentDb
Set rstSource = db.OpenRecordset("tblSource", dbOpenForwardOnly, dbReadOnly)
Set rstDestination = db.OpenRecordset("tblDestination", dbOpenForwardOnly,
dbAppendOnly)

With rstSource
Do Until .EOF
rstDestination.AddNew
For Each fld In .Fields
rstDestination.Fields(fld.Name) = .Fields(fld.Name).Value
Next fld
rstDestination.Update
.MoveNext
Loop
End With

Exit_Here:
rstSource.Close
Set rstSource = Nothing
rstDestination.Close
Set rstDestination = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Exit_Here

End Sub

 

[Guest]

How would I do it with an append query? I have the recordset created in code

 

[Guest]

This is the code for creating the recordset that I need to dump the data from
into a table...

Set qd = con.CreateQueryDef("", sql8)
Set rs = qd.OpenRecordset

With rs
I = 1
'Dim PlantInfo(30, 6) As Variant
Dim OutageType(1000, 30)
Dim StartDate(1000, 30)
Dim EndDate(1000, 30)
Dim MWBefore(1000, 30)
Dim MWAfter(1000, 30)
Dim NERCID(1000, 30)
Dim CAUSECODE(1000, 30)
Dim COMPID(1000, 30)
Dim OUTAGENUMBER(1000, 30)
Dim Extreme(30, 2) As Date ' 1 is the start,
' j =1 FACILITY j = 2 NERCUNITID j = 3 MDC j = 4 UTYPE j = 5
PPL_SHARE j = 6 Plant Price Point
Dim kay(30) As Integer
For aka = 1 To Numplants
kay(aka) = 0
Next aka

Do While Not .EOF
j = 1
pick = "maybe"
Do While pick = "maybe"
If (!NERC_UNIT_ID) = PlantInfo(j, 2) Then
pick = "yes"
plantdex = j
End If
If j > Numplants Then
pick = "no"
End If
j = j + 1
Loop

If pick = "yes" Then

kay(plantdex) = kay(plantdex) + 1
OutageType(kay(plantdex), plantdex) = (!OUTAGE_TYPE)
StartDate(kay(plantdex), plantdex) = (!START_DATE)

EndDate(kay(plantdex), plantdex) = (!END_DATE)
MWBefore(kay(plantdex), plantdex) = (!MW_BEFORE_RED)
MWAfter(kay(plantdex), plantdex) = (!MW_REDUCTION)
NERCID(kay(plantdex), plantdex) = (!NERC_UNIT_ID)
OUTAGENUMBER(kay(plantdex), plantdex) = (!OUTAGE_NUMBER)

End If


I = I + 1
.MoveNext
Loop
End With

 

[Arvin Meyer [MVP]]

I would create the query without opening the recordset. You can do that by
creating the append query first and calling it; or by creating and saving a
querydef, then calling it; or by appending the data from a select statement.
I usually do the first, and occasionally the last. I haven't used the second
method in some time because it involves doing a lot of checking for the
existence of a querydef and deleting, etc. which takes more time to both
write and execute.

 

[Arvin Meyer [MVP]]

Looks like a temporary querydef from Oracle data, but without a select
statement, it doesn't make much sense to me.

 

[Guest]

Here is the sql statement:

sql8 = "SELECT INPUT_DATA.OUTAGE_NUMBER, INPUT_DATA.CAUSE_CODE,
INPUT_DATA.OUTAGE_TYPE, INPUT_DATA.START_DATE, INPUT_DATA.END_DATE,
INPUT_DATA.MW_BEFORE_RED, INPUT_DATA.MW_REDUCTION, INPUT_DATA.NERC_UNIT_ID" &
Chr(13) & "" & Chr(10) & "FROM GENCO.INPUT_DATA INPUT_DATA" _
& Chr(13) & "" & Chr(10) & "WHERE (INPUT_DATA.END_DATE>='" & sdate & "')
AND (INPUT_DATA.OUTAGE_TYPE<>'RS') AND (INPUT_DATA.START_DATE<'" & edate &
"') OR (INPUT_DATA.END_DATE Is Null) AND (INPUT_DATA.OUTAGE_TYPE<>'RS') AND
(INPUT_DATA.START_DATE<'" & edate & "')" & Chr(13) & "" & Chr(10) & _
"ORDER BY INPUT_DATA.NERC_UNIT_ID, INPUT_DATA.START_DATE,
INPUT_DATA.OUTAGE_TYPE" _

Then the rest of the code...
Set qd = con.CreateQueryDef("", sql8)
Set rs = qd.OpenRecordset

Dim kaystart As Integer
Dim mult As Double

For I = 1 To kay(aka)
fakeout1 = CDate(Month(StartDate(I, aka)) & "/" & Day(StartDate(I, aka))
& "/" & Year(StartDate(I, aka)))
fakeout2 = CDate(Month(EndDate(I, aka)) & "/" & Day(EndDate(I, aka)) &
"/" & Year(EndDate(I, aka)))
For j = 1 To Iprice

If price_date(j) = fakeout1 Then
Thehour = Hour(StartDate(I, aka))
kaystart = Thehour + 1
Theminute = Minute(StartDate(I, aka))
mult = (60 - Theminute) / 60
OutageCost(I, aka) = mult * (price(j, Thehour) - fuelcost(j,
Thehour, aka)) * MWAfter(I, aka)
For k = kaystart To 24
OutageCost(I, aka) = OutageCost(I, aka) + (price(j, k) -
fuelcost(j, k, aka)) * MWAfter(I, aka)
Next k
End If
If price_date(j) > fakeout1 And price_date(j) < fakeout2 Then
For k = 1 To 24
OutageCost(I, aka) = OutageCost(I, aka) + (price(j, k) -
fuelcost(j, k, aka)) * MWAfter(I, aka)
Next k
End If
If price_date(j) = fakeout2 Then
Thehour = Hour(EndDate(I, aka))
kayend = Thehour
Theminute = Minute(EndDate(I, aka))

mult = (Theminute) / 60

OutageCost(I, aka) = OutageCost(I, aka) + mult * (price(j,
Thehour) - fuelcost(j, Thehour, aka)) * MWAfter(I, aka)
For k = 1 To kayend
OutageCost(I, aka) = OutageCost(I, aka) + (price(j, k) -
fuelcost(j, k, aka)) * MWAfter(I, aka)
Next k
End If

Next j
Next I

cakk = 1
For cak = 1 To kay(aka)
cakk = cakk + 1
Next cak

 

[Arvin Meyer [MVP]]

Just use your select statement in an append query:

INSERT INTO TableToAppendTo( Column1, Column2)
SELECT Field1, Field2
FROM qry1;

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com