Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update data between tables with VBA in ACCESS 2010

Posted on 2015-01-12
3
Medium Priority
?
355 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
[X]
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
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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