recordset handling question

[tdr]

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.
I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

[Scott McDaniel]

On 29 May 2007 04:58:08 -0700, tdr <tr****@gmail.c omwrote:

>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.

How are you insuring that the row in Table1 is being compared to the correct row in Table2?

>
I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

You can use the SELECT INTO statement to build a new table:

SELECT * INTO Table3 FROM Table1

This will create Table3 from TAble1 ... note that Table3 cannot exist, or an error occurs. Select Into supports the use
of Joins and such, so you could build a query that returns only the unique records in Table1, or in Table2 ...

See here for info on Select Into:
http://www.w3schools.com/sql/sql_select_into.asp

>
I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

Scott McDaniel
scott@takemeout _infotrakker.co m
www.infotrakker.com

[Tom van Stiphout]

On 29 May 2007 04:58:08 -0700, tdr <tr****@gmail.c omwrote:

This smells like a bad database design, and you're going to compound
it by creating table3. There is no need for table3, because the data
can be calculated in a query:
More than likely there also is no need for table2, and all the data
could be stored in one table, perhaps with an additional flag to
indicate what type of record it is. Bad database design leads to
complications, such as the ones you're now running into.

Chances are you will persist going down this path, so here are a few
hints:
To find records in one table that don't occur in another, don't use a
recordset but use SQL:
select * from table2
where PrimaryKeyField not in (select PrimaryKeyField from table1)
(you DO use primary keys, right?)

To create table3, read up on MakeTable query, and turn the above query
into a MakeTable query.

-Tom.

>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.
I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

[tdr]

i'm already using the select * from option,
I thought it if I already had the recordset I could just write that to
table 3.
Scott McDaniel wrote:

On 29 May 2007 04:58:08 -0700, tdr <tr****@gmail.c omwrote:

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.

How are you insuring that the row in Table1 is being compared to the correct row in Table2?

I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

You can use the SELECT INTO statement to build a new table:

SELECT * INTO Table3 FROM Table1

This will create Table3 from TAble1 ... note that Table3 cannot exist, or an error occurs. Select Into supports the use
of Joins and such, so you could build a query that returns only the unique records in Table1, or in Table2 ...

See here for info on Select Into:
http://www.w3schools.com/sql/sql_select_into.asp

I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

Scott McDaniel
scott@takemeout _infotrakker.co m
www.infotrakker.com

[tdr]

here want I'm trying to do.
I get a text file every day. ( a full load 300,000 records)
I import this file into the database
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)
so I think I need three tables
table 1 - today data
table 2 - yesterdays data
table 3 - changes from table 1. (once formated these will be exported)
Tom van Stiphout wrote:

On 29 May 2007 04:58:08 -0700, tdr <tr****@gmail.c omwrote:

This smells like a bad database design, and you're going to compound
it by creating table3. There is no need for table3, because the data
can be calculated in a query:
More than likely there also is no need for table2, and all the data
could be stored in one table, perhaps with an additional flag to
indicate what type of record it is. Bad database design leads to
complications, such as the ones you're now running into.

Chances are you will persist going down this path, so here are a few
hints:
To find records in one table that don't occur in another, don't use a
recordset but use SQL:
select * from table2
where PrimaryKeyField not in (select PrimaryKeyField from table1)
(you DO use primary keys, right?)

To create table3, read up on MakeTable query, and turn the above query
into a MakeTable query.

-Tom.

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.
I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

[tdr]

here want I'm trying to do.
I get a text file every day. ( a full load 300,000 records)
I import this file into the database
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)
so I think I need three tables
table 1 - today data
table 2 - yesterdays data
table 3 - changes from table 1. (once formated these will be exported)
Tom van Stiphout wrote:

On 29 May 2007 04:58:08 -0700, tdr <tr****@gmail.c omwrote:

This smells like a bad database design, and you're going to compound
it by creating table3. There is no need for table3, because the data
can be calculated in a query:
More than likely there also is no need for table2, and all the data
could be stored in one table, perhaps with an additional flag to
indicate what type of record it is. Bad database design leads to
complications, such as the ones you're now running into.

Chances are you will persist going down this path, so here are a few
hints:
To find records in one table that don't occur in another, don't use a
recordset but use SQL:
select * from table2
where PrimaryKeyField not in (select PrimaryKeyField from table1)
(you DO use primary keys, right?)

To create table3, read up on MakeTable query, and turn the above query
into a MakeTable query.

-Tom.

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.
I can read an entire row/recordset from table 1 into a recordset
using the following
strSQL_in = "select * from table1" ( and i can refer to the columns
as
needed)

but how do i write the entire recordset to table 3 without declaring
each column name.

I have over 70 fields to compare and 100+ fields to write to table 3

i tried the following but it does not pass the error checking of the
compiler.
strSQL_out = "insert into table3" + rsd!

TIA

[Larry Linson]

"tdr" <tr****@gmail.c omwrote

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 misunderstandin g 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

[tdr]

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.OpenRecordse t("SELECT * " & _
" from todays_file ")

Set rs = db.OpenRecordse t("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" <tr****@gmail.c omwrote

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 misunderstandin g 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