Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 21st, 2007, 01:34 AM
Registered User
 
Join Date: Sep 2007
Location: surat, gujarat, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to keturpatel
Default merge Records from 2 databases

Hello Everyone,

  I have 2 databases with same structure but diffrent records.

  All i want to do is merge them in single database. Remember that it contains about 25 tables, many of them has autonumber fields as primery key and all of them are related to each-other with cascade update/delete relationship.

  Is there any way to do so? any software or code.

  Thanks in advance.
Reply With Quote
  #2 (permalink)  
Old September 21st, 2007, 06:27 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This can be difficult and require some and work and coordination since the Primary Keys and Foreign Keys can get messed up. There are few automated ways to do this that I know of. Anyone else?

I can share with you some of the manual ways to do this if that is what you decide to do. How large is the database?

Basically use the larger database to recieve data from the smaller database (as _1 tables), then create the queries to append data from the _1 versions of each table to the target tables. You may need extra columns to store the meanginful data from the look up columns, and then do update queries to update the look up columns to the proper look ups based on the static values in the temporary columns.

Not fun.

mmcdonal
Reply With Quote
  #3 (permalink)  
Old September 21st, 2007, 08:10 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

The only auto solution I can think of is to use Replication which typically you would use if you had your laptop "on-the-road" and would then transfer data to another fixed station PC in an Office.
The involves synchronizing and may not be suitable post data entry in a .mdb file
This may not be what you wanted>

It may be that a later version such as office 2007 might do merges such as this. I do have 2007 but have not checked out this aspect. Currently I am familiarising myself with 2007 totally new User Interface. Not easy

MF

Reply With Quote
  #4 (permalink)  
Old September 22nd, 2007, 12:07 AM
Registered User
 
Join Date: Sep 2007
Location: surat, gujarat, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to keturpatel
Default

Thanks everyone for reply.

i will try you suggetions.

Currently i'm working on suggestion =

step 1 : Take copy of small database and change autonumber fields to long fields

step 2 : update these fields with number greater than those in big database

step 3 : somehow, make big database to allow identity_insert option to true ????

step 4 : use insert into qurey to append data to big database from small database.

hope it will work !!!!
Reply With Quote
  #5 (permalink)  
Old September 22nd, 2007, 09:56 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good Luck

If it is succesfull perhaps you could post a detailed and step by step instruction to the Forum...Something like that I am sure would be of interest to a lot of people

MF

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to merge two records of xml Neha XSLT 6 July 11th, 2008 04:59 AM
problem in updating records & finding records naveed77 VB Databases Basics 1 January 16th, 2007 12:12 PM
problem in updating records & finding records naveed77 VB How-To 1 January 16th, 2007 12:10 PM
How to merge two xmldocuments together? NewToXSL XML 1 June 15th, 2006 12:59 PM
How can I merge two databases and tickets tables? drarem Access VBA 2 October 5th, 2004 01:07 PM



All times are GMT -4. The time now is 10:53 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.