UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Moving Data From One Db To Another, Access 2007    
 
   
KathCobb
post Oct 11 2019, 11:14 AM
Post#1



Posts: 497
Joined: 18-October 06



I have two databases with exactly the same structure. I need to move specific records from the one database and send them into the other. I've never done this before and am very nervous. I am assuming the easiest way is an append query (which I have also never used), I am ok with first copying the data and then deleting it after its successfully loaded into the other DB. I have the proper delete query's for that action already. I'm basically just looking for advice on how to go about it. Its about 3,000 records stored across numerous tables (clients and policies sold to the clients by one specific sales agent). The basics, such as city table, companies and policies types are all in their own tables in both DBs and should have all the same information.

I know backup - backup - backup before trying anything, but any other advice or methods to use would be greatly appreciated.

Thanks,
Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
arnelgp
post Oct 11 2019, 11:27 AM
Post#2



Posts: 1,424
Joined: 2-April 09
From: somewhere out there...


to Insert record from Current database to Another database:

CurrentDb.Execute "INSERT INTO TableNameInAnotherDB IN 'Path\FilenameOfOtherDB.accdb' SELECT * FROM TableInThisDB;"

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2019, 11:47 AM
Post#3


UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA


It is likely that an external query reference like that would be most efficient, especially for a one off merge. However, if this is something you'll need to do repeatedly, we ought to step back and look at other considerations as well.

That said, what I would do is copy both accdbs to a new "testing" folder. Create a series of queries like the one Arnel suggested and run them on these test copies. Note any errors that occur during this test run and decide how to address them.

One problem I can see would be conflicting Primary Key values if you import records into existing tables with their own Primary and Foreign Keys. Depending on your table design, that is quite likely to be a problem. And this seems to suggest it will be: "Its about 3,000 records stored across numerous tables (clients and policies sold to the clients by one specific sales agent)".

Primary and Foreign Key values in those related tables need to be copied correctly to maintain the integrity of the imported data. And that takes planning.

Tell us more about that aspect of it, please.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 11 2019, 11:59 AM
Post#4



Posts: 1,424
Joined: 2-April 09
From: somewhere out there...


you need also to make sure to convert All autonumber field in External db be converted to Long (not autonumber), so it will copy also the autonumber and not alter its value.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
projecttoday
post Oct 11 2019, 12:21 PM
Post#5


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


I'm guessing that you have tables that are common to two different applications. In that case you should put the tables into a database of their own, a backend, and link the other two frontends to it.

--------------------
Robert Crouser
Go to the top of the page
 
dmhzx
post Oct 11 2019, 12:35 PM
Post#6



Posts: 7,056
Joined: 22-December 10
From: England


A few questions:
You say you want to MOVE records from one to another.
Does that mean (which I think it does) take then out of one and put them into another?

Do you need to take related record from one to the other?
That is suppose it's a customer detail. Do you want to MOVE the customer with all their related records ?

Is this a one of or a regular process?

What is your experience with VBA? - I would do this mainly with recordset manipulation and VBA rather than using queries, simply because I feel more in control.


It is quite possible to open two databases in one VBA module.

Id you write down how you would do this manually, then it is possible to code it with VBA (and single step it)

If you do have different Primate keys for the 'same' customer you may need a 'transfer' table that store bots keys, and you can then use query joins to pick up the data you need.

But I think we all need more details about what you want to do, from where we can be more specific about HOW
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 04:43 AM