split the records of one file to multiple file in access through vba code

[perus josh]

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.

[jonh]

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]

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]

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.

[jonh]

No idea sorry.

[perus josh]

thank you Jonh.

[ranman256]

What do you mean split? Split how? Only USA?....

[ranman256]

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]

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

[SamT]

I have merged two threads on the same subject into this one thread.

[ranman256]

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