Access 2007 export specified rows

[quicknk]

Is there a way in MS Access 2007 to export specific rows out of the specified table?
Say I have a database with just 1 table called "contact numbers" and in this table I just have 2 field on is the "ID" field (KEY) the other is called "phone number". I have 100000 Records in this table. How do I export rows 900-40000 in Access without having to figure out if I'm manually selecting the right ones?

I want to export it as a delimited text file, which is easy to do, I just can't figure out how to select the desired rows/records.

[FishVal]

Hi, quicknk.

So basically your question is about how to enumerate records.
To achieve this your table has to contain at least one unique field (primary key will serve well) - let us say its name is [keyID]. The logic of the solution is the following:

• use a value of [keyID] in the first record as mark to start enumeration
• use a function with static variable to hold record number
• each time the function is invoked and gets [keyID] value as argument it returns incremented static variable as record number or 1 if the value of [keyID] is the same as the value in the first record (thats why [keyID] field has to be unique)

You'll need two VBA function:

• the one mentioned above
• a function to get particular field value from the first record of sorted query - native SQL First() function or domain aggregate DFirst() work incorrectly

[code=vb]

Public Function RecordNumber(va rUniqueField As Variant, _
varFirstValue As Variant) As Long

Static lngRecordNumber As Long

If IsNull(varUniqu eField) Or IsNull(varFirst Value) Then Exit Function
If varUniqueField = varFirstValue Then lngRecordNumber = 0
lngRecordNumber = lngRecordNumber + 1
RecordNumber = lngRecordNumber

End Function

Public Function GetFirstValue(s trFieldName As String, _
strDataSetName As String) As Variant

Dim rs As New ADODB.Recordset

With rs
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.ActiveConnecti on = CurrentProject. Connection
.Open strDataSetName
GetFirstValue = .Fields(strFiel dName)
.Close
End With

Set rs = Nothing

End Function

[/code]


Let us say the table has name [tblTable].
The first query ([qrySorted]) just sorts the table by some field:
[code=sql]
SELECT tblTable.*
FROM tblTable
ORDER BY tblTable.[Some field];
[/code]
The second query ([qryEnumerated]) returns enumerated records of [qrySorted].
[code=sql]
SELECT qrySorted.*, RecordNumber(qr ySorted.keyID, GetFirstValue(" keyID","qrySort ed")) AS lngRecordNumber
FROM qrySorted;
[/code]


Thus obtained dataset may be easily filtered by specific record numbers and exported to csv using DoCmd.TransferT ext method.

Regards,
Fish

[quicknk]

I'm lost, what I want to do is do it via Access 2007 through a Macro or Query. I'm new to VB and Accdb's.

[quicknk]

Actually I found using filter works (between specified ID #s) but it will only allow me to copy and paste 65K of them, how do i move the results of a filter to a new table or database?

[FishVal]

Actually I found using filter works (between specified ID #s) but it will only allow me to copy and paste 65K of them, how do i move the results of a filter to a new table or database?

How actually do you perform export and how do you open exported file?

[NeoPa]

If you're exporting to an Excel format file then 65,536 is the maximum you can do. Excel spreadsheets can only go up to a maximum of 65,536 rows.