Help updating a table

[erick-flores]

hello all

I am getting my data from an ODBC connection, using Access 2003. The
application refresh all the data when the user clicks on a button. It
refresh the data by deleting the old table and substitude with a new
table, I have a make table query to do this. All this is working fine,
but now the user wants to keep some of the records from the old table
and when they click on the refresh button it actually refresh BUT it
keep some particular records. The records they want to keep are those
that have a check on a checkbox that represent a paid field.

I was thinking of a update query but im not sure how to do this.

Thanks in advance

 

[Jeff Boyce]

I guess my first question is whether you could just link to the data via
ODBC, and use queries to see only that data you wanted to?

If you have to have a local copy of data, but do not want it over-written
when you pull from your ODBC connection, don't use the Make Table approach.
Instead, as you've discussed, create an append query to add the new records,
and create an update query to modify those for which some of their data has
changed.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[erick-flores]

Thanks for your reply...

How do I create an append query to ONLY add new records?. Because I
tried doing so and it add all the field over the existing ones, so I
have duplicates all over.

Any ideas?

 

[erick-flores]

i am talking about the table that gets created with the append query,
that tables has all the duplicates. Should I use the append query as
my datasource for the form? but then that table will be getting bigger
and bigger...I am confuse

 

[erick-flores]

Here is my SQL

INSERT INTO v_aging_rec
SELECT v_aging_rec2.*
FROM v_aging_rec2;

and I am getting duplicates over duplicates, I dont even get the "You
are about to append ## rows" message box. I dont know if I supposed to
get this or not everytime, but my guess is yes.

Any ideas?

 

[Jeff Boyce]

If the table into which you are inserting new rows has no unique indexes,
and if you are not trying to insert values that conflict into an Autonumber
primary key field, then no, you won't get warned about duplicates because as
far as Access is concerned, there are no duplicates.

If you index the fields on which you do NOT want duplicates, then you should
be getting the warning message when you try to append a "duplicate" record.
If you are not getting the warning, the SetWarnings has been turned off (see
Access HELP on this).

Regards

Jeff Boyce
Microsoft Office/Access MVP