Re: recordset handling question

sorry for the confusion, I left out the compare code because I did not
need help with that part of the code.
I did include the part I need help with (writing the recordset to
table 3),
strSQL_out = "insert into table3" + rsd!

as to row/recordset, i was not sure what other would know it as, so I
stated both row or recordset.

the rest of the posting is just parts of the code i'm using and really
does not need to be looked at, but included if it helps me get an
answer to writing the whole recordset to table 3.

Set rsd = db.OpenRecordset("SELECT * " & _
" from todays_file ")

Set rs = db.OpenRecordset("SELECT * " & _
" from yesterdays_file ")

rs.MoveFirst
rsd.MoveFirst


Do Until rsd.EOF And rs.EOF

If rs.EOF Then
VY_E3PARTIC = "F9999999999999"
Else
VY_E3GROUP = rs!E3GROUP
VY_E3PARTIC = rs!E3PARTIC
VY_E3DEPNO = rs!E3DEPNO
VY_E3EFFDT = rs!E3EFFDT
VY_E3END = rs!E3END .............


If rsd.EOF Then
VP_E3PARTIC = "F9999999999999"
Else
VP_E3GROUP = rsd!E3GROUP
VP_E3PARTIC = rsd!E3PARTIC
VP_E3DEPNO = rsd!E3DEPNO
VP_E3EFFDT = rsd!E3EFFDT
VP_E3END = rsd!E3END.........


myStatus = (VP_E3DEPNO <> VY_E3DEPNO)
myStatus = myStatus Or (VP_E3LAST <> VY_E3LAST) .........


If myStatus = True Then
strSQL_ins1 = "insert into changes_file ........
db.Execute strSQL_ins1
verr = Err.Description
strSQL_ins1 = Nothing

end if
......
myStatus = False
rs.MoveNext
rsd.MoveNext
......
loop

......




Larry Linson wrote:
"tdr" <tross9@xxxxxxxxx> wrote

I need to compare todays file to the file I got yesterday,
if any rows have changed I want to move those to another table to be
exported out to a new text file to be loaded into our master system.
( I need to format these before I export them)

That's certainly different from my understanding of your initial post:

"I need to compare table 1 to table 2 and if the row/recordset in
table 1 is different from table 2, write the entire row/recordset
from table 1 to table 3."

Perhaps my misunderstanding is due to the fact that I am unaware of an
Access object named "row/recordset". "Rows" I know; "recordsets" I know;
"row/recordset" I've never heard of until your post. (Q 1) Which is it: do
you want to move only the Records which differ; or do you want to move the
entire Table? (Q 2) If the latter, do you want to replace the Table or do
you want to Append the new Table (which likely will result in duplicate
keyed records).

I don't believe you answered the question, "How do you know you are
comparing the comparable rows in the tables?" SELECT * FROM SomeTable just
selects all the rows (aka records); it doesn't compare anything. (Q 3)
Please clarify how you are comparing.

You have your database in front of you; we have to rely on your description.
If someone asks you a question or asks for clarification, they may not be
able to continue to help you if you disregard their request. It's a matter
of our knowing what we need to know to be able to assist as opposed to your
deciding what we need to know to be able to assist.

Larry Linson
Microsoft Access MVP

.

Relevant Pages

  • Re: Comparing Two Identical Tables - Loop Through fields in a Recordset
    ... two tables and writing to a recordset of the third table? ... Field7 = GG ... dim db as database, rs as recordset, cSQL as string ... 'COMPARE NUMERIC DATA - ABSOLUTE % CHANGE ...
    (comp.databases.ms-access)
  • Re: recordset.MovePrevious
    ... You need to cut & paste the exact code you are using - from & including the ... > to compare 2 items in a query based recordset. ... > Value is the name of the column in the recordset. ... > to look at the value, store it as a variable, then look at ...
    (microsoft.public.access.modulesdaovba)
  • Re: Update an SQL database
    ... Try using Echo for every row in the recordset, ... If objRecordSet.Fields= strServerName Then ... want to compare my variable with the record in the database, ...
    (microsoft.public.data.ado)
  • Re: Comparing two DataSets with a Merge
    ... An ADO.NET RecordSet keeps track of the previous and current ... You can then call the GetChanges() ... >> been changed over time offsite and saved in an access database. ... >> take advantage of versions in the dataset to compare previous and current ...
    (microsoft.public.dotnet.framework.adonet)
  • recordset handling question
    ... table 1 is different from table 2, write the entire row/recordset ... I can read an entire row/recordset from table 1 into a recordset ... I have over 70 fields to compare and 100+ fields to write to table 3 ... compiler. ...
    (comp.databases.ms-access)