View Full Version : split the records of one file to multiple file in access through vba code
perus josh
05-24-2014, 02:32 AM
Hi,
I have 15 million records in access as one file, i need to split into 1 million each file in access through vba code.
ex. 1 to 1000000 one file
1000001 to 2000000 second file
any one provide the solution
thank you for your help.
If all the data is in a single table you could add an autonumber field and run a bit of sql. e.g.
SELECT *
INTO [c:\newdb1.accdb].newtable
FROM oldtable
WHERE AUTOID between 1 and 1000000
SELECT *
INTO [c:\newdb2.accdb].newtable
FROM oldtable
WHERE AUTOID between 1000001 and 2000000
etc
perus josh
09-12-2014, 11:24 PM
Hi
I have one single file 13 million records, i need to split into 1 million each file.
the records as below.
Comapny
Meridian Wine & Spirits , USA
A E Beach High School , USA
Don Deason Co., Inc. , USA
China Performance, Group. , USA
Hoss's Steak & Sea House Inc , USA
New World Knitting Inc , USA
JOHN FITZPATRICK , USA
Seal & Stripe, Inc. , USA
Groverjackson.com , USA
Carol H Pollard, Psyd , USA
Babye Co Trends , USA
Sunbelt Cranes Construction & Hauling Inc. , USA
Jibben Consulting Services, Inc. , USA
Being, Group. , USA
North American Electric Reliability , USA
Bay Auto Paint And Collision , USA
James R Mobley, Dds , USA
Mountain Park Church , USA
Ashley Home Store , USA
Quicksilver Kites , USA
Great Lakes Chemical Corporation, USA
how to split the multiple file in access through VBA code.
advance thank you
Regards
Josh
perus josh
09-13-2014, 01:50 AM
If all the data is in a single table you could add an autonumber field and run a bit of sql. e.g.
SELECT *
INTO [c:\newdb1.accdb].newtable
FROM oldtable
WHERE AUTOID between 1 and 1000000
SELECT *
INTO [c:\newdb2.accdb].newtable
FROM oldtable
WHERE AUTOID between 1000001 and 2000000
etc
when am running following query
SELECT *
INTO [D:\newdb1.accdb].newtable
FROM company_USA_13m
WHERE ID between 1 and 1000000
the below error message is coming
could not find 'd:\newdb1.accdb'.
Note: even i created newdb1.accdb in d drive
can you help me how to do it.
perus josh
09-15-2014, 01:45 AM
thank you Jonh.
ranman256
09-15-2014, 05:57 AM
What do you mean split? Split how? Only USA?....
ranman256
09-15-2014, 06:14 AM
If you mean just blocks of 1million then another in a separate database
In your export query, set the TOP to 1000000, where [sent] = ""
then mark those TOP mill, set [sent]=true
Then next block export the TOP mill, where [sent] = ""
continue until all records sent
perus josh
09-15-2014, 07:02 AM
HI Ranman,
yes, one million each first file ex.1 to 1000000, second file is 1000001 to 2000000, third file 2000001 to 3000000 so it will split till the end of record.
after split this in access, i need to export to excel (.xlsx .xls or .csv)
to do this I need VBA code. (exact requirement is split large count records file into less count records file)
thanks for your help in advance..
regards
Josh
I have merged two threads on the same subject into this one thread.
ranman256
09-18-2014, 06:29 AM
You have a query to pull TOP 1million records (unmarked)
then after the export
mark a field as true from the TOP 1mill
repeat if any are still unmarked.
Sub ExportMillions()
Dim vFile, vBase
Dim i As Integer
'set the TOP to 1000000
'QRY: qs1MillionRecs
vBase = "c:\filepath\filename"
i = 1
'see if there are any unmarked recs (it sets a the [MARKED] field = true)
While DCount("*", "qsUnmarkedRecs") > 0
'set filename
vFile = vBase & i & ".xls"
'export the file
DOCMD.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qs1MillionRecs", vFile, True
DOCMD.OPENQUERY "quMark1Million" 'this marks the TOP 1M recs
i = i + 1
Wend
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.