append data from one database to another

[saurabhlotankar]

Hello All,

I am new forum and MS Access.

I have one access databases. In that database, I have 4 tables with one years data.(i.e. one years data is divided into 4 tables)
Table1 contains Jan, Feb Mar data,
Table2 contains Apr May Jun data and so on for remaining 2 tables.

I want to keep the entire year's data into one table only. I tried appending it into a new table but size goes beyond 2gb and hence not possible.

What I am thinking is to create a fresh blank database and append data from these tables into newly created database(in one table)

Can someone tell me the step by step procedure.

Thanks

 

[theDBguy]

Hi. Assuming it will work (don't know if the size limit will still be a problem), you could link those tables into a new database file and then append their contents into a new local table. Here are the steps:
1. Create a new database file and open it
2. From the External Tab on the Ribbon, select import data from Access database
3. Select the "link" radio button and locate the old file, then click OK
4. Select the four tables to link to and then click OK
5. Create a new query in design view and select one of the linked tables
6. Drag all the fields into the design grid
7. Change the query type to a Make-Table query by clicking on the Ribbon button
8. Give it a name and click OK then Run the query
9. Repeat the above steps 5-8 with the other tables
The above steps may not be exact since I wrote them from memory. Good luck!

 

[Deleted member 147267]

Isn't that going to result in 4 new tables rather than one as is wanted? I'm reading that you're suggesting 4 make table queries. Maybe 1 make table query then switch it to append for the other 3?

Will probably find out that 4 tables have one or more object fields in them, thus the size problem.

 

[theDBguy]

Sorry, Micron is correct. I was thinking it but forgot to say after the first one, we need to use APPEND queries next. Thanks for the assist.

 

[The_Doc_Man]

If the homogeneous MAKE-TABLE + APPEND case would exceed 2 Gb, there is a danger that you have more than 1 Gb of data in the four quarterly tables. Working with tables this large is possible with Access, but there are issues in terms of "database bloat" if you do a lot of updating, inserting, and deleting.

If this is a relatively static database, you would be OK in making the links as suggested by the DB guy and making the first-quarter table, then appending the 2nd, 3rd, and 4th quarter tables. In this case, where you are using it for some sort of aggregate reports or perhaps some selective reports, you should still be OK.

If this data gets "churned" a lot (insertions, updates, and deletions), you may have already exceeded the size that is optimal for Access databases and would need to switch to some sort of SQL-based utility for the back-end file.

Without knowing more about the end use of that big table, it will be hard to decide.

Note also that it is easily possible to have more than one back end. So you COULD in theory build a single back-end for the big table and a different (probably a lot smaller) back end for other data, then control them both from the same front end. You can use up to 16 database files, and this would represent only 3 files (FE, 2 x BE). It should work just fine. But if the big DB isn't relatively stable, then maybe it isn't so good.

 

[Mark_]

Are your tables this large because each tables has millions of records OR are the tables this large because each records is very large?