Access does not "number" records. At least, you can't rely on the first 50,000 being the same each time you run a query, *unless* you sort them. You can add a unique incremented key, such as autonumber to help you identify them.
There's a couple approaches.
The first that sounds the easiest would be to use the TOP modifier
(SELECT TOP 50000 fld1, fld2, fld3 FROM tblName ORDER BY fld1, fld2) and then export the query into Excel. And then repeat in some fashion that excludes the first batch (or 2nd/3rd if necessary)
With a unique incremented key (add Autonumber field and it fills them in) you can show a parameter.
SELECT fld1, fld2, fld3 FROM tblName WHERE fld1<50001
The better way is to export recordsets that you figure out via VBA code.
Take a look at this:
http://www.mvps.org/access/modules/mdl0035.htm
I'd recommend the third function.
sCopyRSToNamedRange
With a modification.
Mike
Code:
Sub sCopyRSToNamedRange()
'Copy records to a named range
'on an existing worksheet on a
'workbook
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "SomeSheet"
Const conWKB_NAME = "c:\temp\book1.xls"
Const conRANGE = "RangeForRS"
Dim strSQL As String
Set db = CurrentDb
Set objXL = New Excel.Application
strSQL = "your_SQL_query_here"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
