Solved

update data between tables with VBA in ACCESS 2010

Posted on 2015-01-12
3
324 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

896 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now