Updating Records

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I need to see if someone has a quick solution or possibly
a snippet of code to help me accomplish the following.

I have table A which contains about 50 fields for each
particular record. The Primary Key is a Case ID which is
unique to each record.

What I need to be able to do is take another table that
has the same fields but only lists the records that have
been modified in some way. I need to have table b find
matches by "Case ID" in Table A and update those records
in A.

So if I have a ticket in table a with a CASE ID of 1275.
and that record is updated by a user I need an easy way to
have access update the record in Table A.

Unfortunately I can't have the record in Table A update as
the information is being processed out of a different
application and as of right now we don't have any sort of
ODBC connection to pull it directly out so I have to
manually update the database.

Any help that you guys can offer will be greatly
appreciated.

Tom
 
Tom,

Access has a built-in way of doing this. It's called Replication. The
basic concept it that you make a Replica copy of your database, via the
Tools|Replication|Create Replica menu, to be used in the remote
location. Then, you can Synchronize the two databases to update each
with the changed data in each.

In the meantime you can do your immediate task with an Update Query...
1. Make a new query in design view
2. Add both Table A and Table B and join on the Case ID field from both
3. Place the fields from Table A which could have changed data into the
query grid
4. Make it an Update Query (select Update from the Query menu)
5. In the Update To row of each field in the grid, put the equivalent
of [Table B].[YourFieldName]
6. Run the query (click the toolbar button with the red ! icon)
 
I have table A which contains about 50 fields for each
particular record. The Primary Key is a Case ID which is
unique to each record.

What I need to be able to do is take another table that
has the same fields but only lists the records that have
been modified in some way. I need to have table b find
matches by "Case ID" in Table A and update those records
in A.

So if I have a ticket in table a with a CASE ID of 1275.
and that record is updated by a user I need an easy way to
have access update the record in Table A.
Click to expand...

Steve's Replication suggestion is one possibility; it's finicky and
needs to be set up with considerable care. Check Michka's website
http://www.trigeminal.com and search for his excellent Replication
whitepaper if you decide to do this.

To "roll your own", it's not hard to do this with an Update query.
Create a Query joining TableA to TableB by Case ID. Apply any desired
criteria to limit which records are retrieved. Change it to an Update
query and under the fields that you want to update in TableA put

[TableB].[Fieldname]

using the corresponding field in TableB. Run the query using the !
icon, or once you have the query stored, simply doubleclick on the
query name and accept Access' offer to update the table, or execute
the query from VBA code.
 
Back
Top