Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

update data between tables with VBA in ACCESS 2010

Posted on 2015-01-12
3
335 Views
Last Modified: 2015-01-12
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
Comment
Question by:ssmith94015
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40545646
>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
 

Author Closing Comment

by:ssmith94015
ID: 40545687
WORKKKKSSSS!!!!  I was worried it would not pick up correctly for the next auto number, but seems to be ok!  Thank you.

Sandra
0
 
LVL 18

Expert Comment

by:Simon
ID: 40545695
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question