Solved

How to split large Access table into 2 smaller tables

Posted on 2013-11-25
19
4,209 Views
Last Modified: 2013-12-10
Greetings Experts!

I have a very large table in Access 2010 that I want to split into 2 smaller tables.  I don't care which records end up in each file.  I know some SQL and some VBA but I wouldn't consider myself an expert with either.

I'm having 2 issues right now:

1. what's the fastest way to get an accurate record count?  I know I can open the table and go to the last record but that's taking forever and Access seems to hang when I do that ....

2.  once I know how many records I have, is there a way to do a Make Table query based on record count (i.e. put the first  X number of records in Table1 and the rest in Table2?

Also open to any other suggestions you may have to help me get this done.

Many thanks, in advance, for your help!
0
Comment
Question by:monkeybiz12345
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 300 total points
ID: 39676487
do you have a unique record id in your table?

to get quick record count of the records from the table, you can use
a query

select count(*) from tablename

if this is really a large table, i will use recordsets to create the two tables..


give more info about your table "name of table" ,"name of fields"


.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39676551
Arbitrarily splitting the table into two pieces will make it difficult to work with.  What kind of processing do you need to do against this table?

Proper indexing will help the query engine to return results more quickly.
0
 

Author Comment

by:monkeybiz12345
ID: 39676571
This data will be ultimately exported as text and used to feed a 3rd party program that has a maximum file size it can process.  

The whole process goes something like this:
- file is too big for 3rd party program to process
- import big file into Access table
- split it into 2 smaller Access tables
- export each smaller table as text creating 2 smaller files
- feed each smaller file to the 3rd party process

This is a limited use thing, too.  I'll make sure it works in the test environment, then do it
in the live environment, then never use it again.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39676574
so, did you try  my suggestion ?
0
 

Author Comment

by:monkeybiz12345
ID: 39676580
capricorn1,

There are 2 fields that make up a unique key - workIDnum and LineNum but no indexes
have been created in Access.
0
 

Author Comment

by:monkeybiz12345
ID: 39676582
capricorn1,

Counting now.... Yes! I have 2,604,159 records.
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 39676591
Open a module and view the immediate window.

Then type the following in the immediate window using your tablename.

intX = DCount("*", "tablename")

Then type ? intX to print the answer.

?intX
 52
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39676592
do a compact and repair and post how big is the db.

you might have to create the two tables in a separate db if the size will reach 2gb if the two tables will be created in the same db.


and, add an autonumber field to the table (if you don't have one)
this will make it easier to split the table.
0
 

Author Comment

by:monkeybiz12345
ID: 39676602
capricorn1,

Yes, the compacted & repaired database files is 1.5 GB.  I'll need to create the smaller tables in a different database.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39676623
here is what you will do.
1. create a make table query like  this query
    SELECT [TableName].* INTO table1 IN 'D:\Database_Ref\DBExampleEE.mdb'
FROM TableName
WHERE  1=0

2 create the second table

SELECT [TableName].* INTO table2 IN 'D:\Database_Ref\DBExampleEE.mdb'
FROM TableName
WHERE  1=0


that will create two blank tables with the same structure as your original table

in 'D:\Database_Ref\DBExampleEE.mdb'
.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 39676654
You don't actually have to write the records to split tables, you can use a query in the TransferText and write the records directly to the export file  If you think the recordIDs are evenly split between odd and even, write the oddnumbers to flA.txt and write the even to flB.txt.
0
 

Author Comment

by:monkeybiz12345
ID: 39688787
@capricorn1,

I don't need 2 empty tables.  I need half the records in Table1 and half in Table2.  I will try your suggestion though.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39688814
@ monkeybiz12345,

that is just the first half of the solution, since you did not respond to the question about autonumber field.
0
 

Author Comment

by:monkeybiz12345
ID: 39688836
Ah... I missed the part about adding an autonumber field. Will do that...Thank you.
0
 

Author Comment

by:monkeybiz12345
ID: 39688891
@capricorn1,

I've added an autonumber field to my table and created the 2 empty tables in another DB as you suggested.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39689056
Did you try the query method?  It doesn't create permanent files or bloat your database.
0
 

Accepted Solution

by:
monkeybiz12345 earned 0 total points
ID: 39699212
I ended up doing 2 delete processes.  First I deleted half the records in my file and used the resulting smaller file to feed the outside process.  Then I repeated this, deleting the other half of the records.  

Not exactly what I had in mind but it got the job done....
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 39699263
Sometimes you have to use a ball peen hammer instead of a velvet cloth.
0
 

Author Closing Comment

by:monkeybiz12345
ID: 39708090
Though neither expert provided a complete answer to my original question, I want to award points to those who participated in the conversation.  Capricorn1 provided the Count syntax I asked for and PatHartman suggested using TransferText.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

20 Experts available now in Live!

Get 1:1 Help Now