Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

update data between tables with VBA in ACCESS 2010

I have two tables that hold almost the same data, but each has over 200 columns.  the key between them is the DealID.  The columns that match have the same name, but the destination tables does not have all the columns that are in the source table.  So, I need to update the destination table field with the corresponding data from the source table but if there is no matching column, ignore it.  I have a query in the database with returns the all the rows since there are matching DealID, but now I need to loop through and put the source data n the destination and not sure how to approach.  should I create two recordset queries in the VBA procedure and match on the field names?

Sandra
0
ssmith94015
Asked:
ssmith94015
  • 2
1 Solution
 
SimonCommented:
>should I create two recordset queries in the VBA procedure and match on the field names?
Yes, that would work.

Or, try this, if your destination table doesn't have relationships with enforced referential integrity.
1. make a copy of your intended destination table and delete all the records.

2. create an append query in the query builder specifying your empty test table as the destination - add your source table to the tables area, double click in the header to select all its columns and drag them to the grid, then run the query.

3. Inspect your test table - it will have retained all the same auto-number column entries and auto-matched all the columns by name.

4. If happy with the result, use your real destination table as the append destination.

Note that Access will allow you to append your values to the auto-number primary key column as long as the table is empty.
0
 
ssmith94015Author Commented:
WORKKKKSSSS!!!!  I was worried it would not pick up correctly for the next auto number, but seems to be ok!  Thank you.

Sandra
0
 
SimonCommented:
Cool :) I think Access uses some kind of internal DMAX(yourAutonumberField)+1 to increment the autonumber columns, but you can actually insert any number that isn't already present on another row. It's not as rigorous as an IDENTITY column in MSSQL.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now