Hi Keith,
As you don't have a record ID, things are a little more complicated.
But I have a solution for you, before you go to the code, read this first.
As a first step, make a copy of your table (or entire mdb/accdb).
The copy is referred to as MotherTableX from now on.
Now make an empty copy (so only copy the structure without data) of MotherTableX, this copy is referred to as tempTableX.
In the code you'll find 5 string functions to create SQL statements:
SQL_FetchTempTableX is used to fetch all records from the tempTableX
SQL_DelFromTableX is used to empty tempTableX
SQL_TOP1Mil is used to create a queryDef to delete the Top 1 million records from MotherTableX
SQL_InsertTOP1Mil is used to insert 1 million records from MotherTableX into tempTableX
SQL_AllRecords is only for a recordcount of MotherTableX
Of course you'll have to replace the table names and field names corresponding to your tables.
Now, how it works is as follows.
From MotherTableX the first 1 million records are inserted into the table tempTableX. From tempTableX the records are passed to a new excel sheet. Then the first 1 million records from MotherTableX are deleted, tempTableX is cleared and the next 1 million are copied to the tempTable.
This will be repeated until all records are deleted from MotherTablex.
Of course you'll have to be patient if you run this code with 14 million records.
Just create a new module in Access and paste the code, don't forget to set the references needed.
Code:
Option Compare Database
Option Explicit
'Set reference to Microsoft Excel xx Object Library
'Set reference to Microsoft ActiveX Data Objects 2.x Library
'Free to use, coded by Johan Kreszner
Public Sub ToExcel()
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim qDef As QueryDef
Dim oExcelApp As New Excel.Application
Dim oExcelWB As New Excel.Workbook
Dim oExcelSht As Excel.Worksheet
Dim sWorkbookName As String
Dim sSheetName As String
Dim lMaxRecPerSheet As Long
Dim iNumbSheets As Integer
Dim iSheetCnt As Integer
Set oCn = CurrentProject.Connection
'Here comes your path and workbookname
sWorkbookName = [B][COLOR=red]"M:\CreateExcel\MyExcelBook"[/COLOR][/B]
'First calculate number of sheets needed, if you know the exact number of records, you don't need this section
'Instead set iNumbSheets = Round((TheNumberOfRecords / lMaxRecPerSheet) + 0.5)
'and remark the code that opens the recordset
[COLOR=royalblue][B]lMaxRecPerSheet = 1000000[/B][/COLOR]
With oRs
'Open Mother to find recordcount
.Open SQL_AllRecords, oCn, adOpenStatic, adLockReadOnly
iNumbSheets = Round((.RecordCount / lMaxRecPerSheet) + 0.5)
.Close
End With
Set oRs = Nothing
'create Excel object
With oExcelApp
'Create a new workbook
Set oExcelWB = Workbooks.Add
With oExcelWB
'Create the sheets
For iSheetCnt = 1 To iNumbSheets
Set oExcelSht = .Worksheets().Add
'Fill tempTableX with top 1million records from MotherTableX
oCn.Execute (SQL_InsertTOP1Mil)
'Fetch all records from tempTableX
oRs.Open SQL_FetchTempTableX, oCn, adOpenStatic, adLockReadOnly
'Create a name for the new sheet
sSheetName = "Sheet " & iSheetCnt & " of " & iNumbSheets
'Copy recordset to new sheet
With oExcelSht
.Name = sSheetName
.Cells(1, 1).CopyFromRecordset oRs 'This is where the records are passed to the sheet
oRs.Close
Set oRs = Nothing
End With
'Delete Top 1million records from MotherTableX
Set qDef = CurrentDb.CreateQueryDef("x", SQL_TOP1Mil) 'Create a query def
oCn.Execute ("Delete * from x") 'Use query def to delete the top 1mil from MotherTableX
CurrentDb.QueryDefs.Delete ("x") 'Delete the query def
'Delete all records from tempTablex
oCn.Execute (SQL_DelFromTableX)
Next iSheetCnt
End With
oExcelWB.SaveAs sWorkbookName
'Destroy object
.Quit
End With
End Sub
Public Function SQL_FetchTempTableX() As String
SQL_FetchTempTableX = "Select * From tempTableX"
End Function
Public Function SQL_DelFromTableX() As String
SQL_DelFromTableX = "Delete * From tempTableX"
End Function
Public Function SQL_TOP1Mil() As String
SQL_TOP1Mil = "SELECT Top 1000000 * FROM MotherTableX"
End Function
Public Function SQL_InsertTOP1Mil() As String
SQL_InsertTOP1Mil = "INSERT INTO tempTableX ( [B][COLOR=red]Field1, Field2, Field3[/COLOR][/B] )" _
& "SELECT TOP 1000000 [COLOR=red][B]MotherTableX.Field1, MotherTableX.Field2, MotherTableX.Field3[/B][/COLOR] " _
& "FROM MotherTableX"
End Function
Public Function SQL_AllRecords() As String
'Replace Field 1 with a valid fieldname from your table
SQL_AllRecords = "Select [COLOR=red][B]Field1[/B][/COLOR] from MotherTableX"
End Function
ffice
></o
