Solved

update data between tables with VBA in ACCESS 2010

Posted on 2015-01-12
3
318 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:
SimonAdept 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:SimonAdept
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

747 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

13 Experts available now in Live!

Get 1:1 Help Now