473,837 Members | 1,521 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Append records into another database

I know how to append records from one table to another in the same database,
but I need to be able to append the records from all the tables in one
database into new empty tables in another database. The tables in the
second database would have the same names as those in the first database.

Can this be done and if so how?

dixie
Jan 29 '06 #1
3 7239
One table at a time.

Jan 29 '06 #2
If you are starting a new database with just the data and no other
objects such as queries, forms, reports and modules, the most efficient
way may be, from the old database, to run
SaveAsText 6, "", NameofNewDataBa se
THIS WILL OVERWRITE THE NEW DATABASE IF IT ALREADY EXISTS so don't use
it unless this is exactly what you want to do (create a new db with
just the Tables indexes and relationships part of the old db).
example
SaveAsText 6, "", "db1.mdb"
(if you already have a db1.mdb it will be GONE FOREVER!)

If that's not what you want to do you can use:
CurrentDb.Execu te "INSERT INTO BCustomers SELECT * FROM [C:\Documents
and Settings\Lyle Fairfield\My
Documents\Acces s\Northwind.mdb].[Customers]"
for each table.

If you want to program all the table names you could loop throught the
tabledefs collection, examine the msysobjects tables, or use the ADO
openschema method.

Jan 29 '06 #3
Unfortunately Lyle, the database already exists and has all queries, forms,
reports and modules in place.

What I am trying to achieve is an upgrade of tables where I simply append
existing data from the older database into tables with new fields and some
altered properties in the new database.

Currently, I do this manually by importing the old tables into the new
database, then running individual append queries to append the old data onto
the new empty tables.

What I would like to be able to do is automate this to the stage that the
new database would import the new data and append it onto the empty new
tables.

dixie

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
If you are starting a new database with just the data and no other
objects such as queries, forms, reports and modules, the most efficient
way may be, from the old database, to run
SaveAsText 6, "", NameofNewDataBa se
THIS WILL OVERWRITE THE NEW DATABASE IF IT ALREADY EXISTS so don't use
it unless this is exactly what you want to do (create a new db with
just the Tables indexes and relationships part of the old db).
example
SaveAsText 6, "", "db1.mdb"
(if you already have a db1.mdb it will be GONE FOREVER!)

If that's not what you want to do you can use:
CurrentDb.Execu te "INSERT INTO BCustomers SELECT * FROM [C:\Documents
and Settings\Lyle Fairfield\My
Documents\Acces s\Northwind.mdb].[Customers]"
for each table.

If you want to program all the table names you could loop throught the
tabledefs collection, examine the msysobjects tables, or use the ADO
openschema method.

Jan 29 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
23962
by: Jonathan Buckland | last post by:
Can someone give me an example how to append data without having to load the complete XML file. Is this possible? Jonathan
2
5791
by: JMCN | last post by:
hi i need some advice on whether if it would be better to use an append query or an update query. here is the situation, i have linked another database table to my current database. then i created a query for the linked database but eventually i need to have this query to be constantly updated and append to another table in the current database. i hope i'm not too vague but i have no idea if i should use an update or an append query....
9
4518
by: JMCN | last post by:
hi- i have inherited an access 97 database that keeps track of the loans. i have been running into referential intergrity problems when i try to append new loans to table. first of all is a linked table from another ms database. when i try to run the append query, i receive an error message that it cannot append all the records due to key violations to the . so i looked at all of the relationships and
2
5790
by: JMCN | last post by:
hi i have a general question regarding append queries in access 97. each week i need to update my table(tblonlinereg) with new or modified records. firstly, i import the text file into my database and then i create a basic append query that appends the new records to the table(tblonlinereg). this works great if the field is greater than the last record appended ( where: tempID > 198 ) but how would i update/append the modifications...
1
2487
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to two csv files. (One for updated data, and the other for unupdated data.) The CSV files are attached to my Jobcosting database. After the CSV
2
3573
by: Mark | last post by:
I have a FE/BD 2002 DB on a XP pro platform. I know this is ugly but it works for me...... A text file is produced from our Oracle WMS. (Average 20k records) A command button deletes all records from a 1 field input table and then opens a form bound to this table. Results from the oracle query are pasted into the input table. When the form is closed, a destination table is cleared of all records and then each record from the input table...
13
4235
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a solution here - until now. This one is driving me crazy. I am making my first attempt at creating a runtime application. I am using Access 2003 Developer Extensions. Initially I developed the database without planning on creating a runtime app...
5
5386
by: Michael C via AccessMonster.com | last post by:
Hello, I have a table that I am appending 3 seperate tables into. My main problem is that each time I append the data, it simply adds to the data already there. That might sound ok, except that if I append the data 3 times in succession, it copies the same data over 3x. Now I have copies in triplicate. It used to only transfer records that weren't already there, but not anymore. If I can't get the append to append correctly, I was...
3
8092
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10872
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10565
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10270
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9397
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7000
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4474
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4040
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.