How to SELECT top 65,000 rows?

Great! Looks like that will work. What if I need to query the (N+1)th through (N+N)th row? In other words, N rows in the middle of the table.

<<Great! Looks like that will work. What if I need to query the (N+1)th through (N+N)th row? In other words, N rows in the middle of the table.>>

 You can't do that.  You'd need to filter the recordset.

 if your looking to get 65,000 random records, see here:

http://support.microsoft.com/kb/170986

JimD.

hmmm...if you specifically want the 10,001st through 74,999th record, your best bet is probably to add an ID field.  Basically, copy your table structure and add a new auto-number field, run an append query to insert all the data from the existing table into the newly created table and then SELECT * FROM NewTable1 WHERE ID > 10,000 AND ID < 75,000.

If you just want it to mix up which 65K records it displays, JimD has got that one covered.

kmt

So the only way to SELECT N = 65,000 rows from the middle of a table is to create an Index column and use...

SELECT * WHERE Index > 65001 AND Index < 130000

...?

Even with VBA it can't be done?

<<You can't do that.  You'd need to filter the recordset.>>

<<Even with VBA it can't be done?>>

 I should have been more specific.  Of course you can do that with VBA.  What I meant was strictly with SQL.  Even adding a autonumber to index on won't really help as autonumbers are not always conseuctive (may be an exception in this case though if your not deleting from the table).

 But with VBA, sure, you can open a recordset, move into the middle of the table, and then fetch the next 65,000 records for whatever purpose.  

JimD.

You could try something like this.  Please note: this is untested

Sub GrabMiddleRecords()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim strConn As String
   Dim cmdT as ADODB.Command
   Dim

   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\yourdb.mdb"

   Set conn = New ADODB.Connection
   conn.Open strConn

   Set rst = New ADODB.Recordset
   With rst
      .Open "Select * from ExistingTable", conn, adOpenKeyset, _
          adLockOptimistic, adCmdText
   
      .Move 10000 ' move forward 10000 records so your starting at that point (you could use whatever number)

      for x = 1 to 65000
      Set cmdT = New ADODB.Command
      With cmdT
            .ActiveConnection = CurrentProject.Connection
            strSQL = "INSERT INTO NEWTABLE (field1, field2, field3) VALUES (rst("Field1"), rst("Field2"), rst("Field3))
            .CommandText = strSQL
            .CommandType = adCmdText
            .Execute
        End With
      next

      .Close
   End With

   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

I have to say that if I were going down the VBA route with this I'd do so partially only.
For example loading the recordset containing only an ordered and uniquely identifying column. (An AN PK is the obvious choice - but not necessarily).

.Open "Select PKID from ExistingTable ORDER BY PKID", conn, adOpenKeyset, adLockReadOnly, adCmdText

That it's read only is no accident either.
You choose anything to get you the most efficient recordset you can for purely fetching the values. (Forward only might be tempting - but Keyset could well edge it for PK only fetching).

lngStart = rst(0)
rst.Move 9999
lngEnd = rst(0)

Then, armed with the PK values (in order) between which you want to select you could execute your SQL statement.

CurrentProject.Connection.Execute "INSERT INTO NEWTABLE (field1, field2, field3) SELECT field1, field2, field3 FROM ExistingTable WHERE PKID BETWEEN " & lngStart & " AND " & lngEnd

The avenue for discontinuity comes from the time lag that exists between determining these values and then executing the subsequent statement using them. During that interval your data set may have changed due to external influences. Again this is very unlikely if you're using a sequential Autonumber field unless your inserts are coded to deliberately find gaps.

Cheers.