Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6493
  • Last Modified:

How to split large Access table into 2 smaller tables

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
monkeybiz12345
Asked:
monkeybiz12345
  • 9
  • 5
  • 3
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
PatHartmanCommented:
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
 
monkeybiz12345Author Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Rey Obrero (Capricorn1)Commented:
so, did you try  my suggestion ?
0
 
monkeybiz12345Author Commented:
capricorn1,

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

Counting now.... Yes! I have 2,604,159 records.
0
 
Emil_GrayCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
monkeybiz12345Author Commented:
capricorn1,

Yes, the compacted & repaired database files is 1.5 GB.  I'll need to create the smaller tables in a different database.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
PatHartmanCommented:
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
 
monkeybiz12345Author Commented:
@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
 
Rey Obrero (Capricorn1)Commented:
@ monkeybiz12345,

that is just the first half of the solution, since you did not respond to the question about autonumber field.
0
 
monkeybiz12345Author Commented:
Ah... I missed the part about adding an autonumber field. Will do that...Thank you.
0
 
monkeybiz12345Author Commented:
@capricorn1,

I've added an autonumber field to my table and created the 2 empty tables in another DB as you suggested.
0
 
PatHartmanCommented:
Did you try the query method?  It doesn't create permanent files or bloat your database.
0
 
monkeybiz12345Author Commented:
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
 
Emil_GrayCommented:
Sometimes you have to use a ball peen hammer instead of a velvet cloth.
0
 
monkeybiz12345Author Commented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now