monkeybiz12345
asked on
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
so, did you try my suggestion ?
ASKER
capricorn1,
There are 2 fields that make up a unique key - workIDnum and LineNum but no indexes
have been created in Access.
There are 2 fields that make up a unique key - workIDnum and LineNum but no indexes
have been created in Access.
ASKER
capricorn1,
Counting now.... Yes! I have 2,604,159 records.
Counting now.... Yes! I have 2,604,159 records.
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
Then type the following in the immediate window using your tablename.
intX = DCount("*", "tablename")
Then type ? intX to print the answer.
?intX
52
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.
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.
ASKER
capricorn1,
Yes, the compacted & repaired database files is 1.5 GB. I'll need to create the smaller tables in a different database.
Yes, the compacted & repaired database files is 1.5 GB. I'll need to create the smaller tables in a different database.
here is what you will do.
1. create a make table query like this query
SELECT [TableName].* INTO table1 IN 'D:\Database_Ref\DBExample
FROM TableName
WHERE 1=0
2 create the second table
SELECT [TableName].* INTO table2 IN 'D:\Database_Ref\DBExample
FROM TableName
WHERE 1=0
that will create two blank tables with the same structure as your original table
in 'D:\Database_Ref\DBExample
.
1. create a make table query like this query
SELECT [TableName].* INTO table1 IN 'D:\Database_Ref\DBExample
FROM TableName
WHERE 1=0
2 create the second table
SELECT [TableName].* INTO table2 IN 'D:\Database_Ref\DBExample
FROM TableName
WHERE 1=0
that will create two blank tables with the same structure as your original table
in 'D:\Database_Ref\DBExample
.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
I don't need 2 empty tables. I need half the records in Table1 and half in Table2. I will try your suggestion though.
@ monkeybiz12345,
that is just the first half of the solution, since you did not respond to the question about autonumber field.
that is just the first half of the solution, since you did not respond to the question about autonumber field.
ASKER
Ah... I missed the part about adding an autonumber field. Will do that...Thank you.
ASKER
@capricorn1,
I've added an autonumber field to my table and created the 2 empty tables in another DB as you suggested.
I've added an autonumber field to my table and created the 2 empty tables in another DB as you suggested.
Did you try the query method? It doesn't create permanent files or bloat your database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sometimes you have to use a ball peen hammer instead of a velvet cloth.
ASKER
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.
Proper indexing will help the query engine to return results more quickly.