By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,060 Members | 553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,060 IT Pros & Developers. It's quick & easy.

Access 2007 export specified rows

P: 3
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.
Mar 1 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 2,653
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

Expand|Select|Wrap|Line Numbers
  2. Public Function RecordNumber(varUniqueField As Variant, _
  3.                              varFirstValue As Variant) As Long
  5.     Static lngRecordNumber As Long
  7.     If IsNull(varUniqueField) Or IsNull(varFirstValue) Then Exit Function
  8.     If varUniqueField = varFirstValue Then lngRecordNumber = 0
  9.     lngRecordNumber = lngRecordNumber + 1
  10.     RecordNumber = lngRecordNumber
  12. End Function
  14. Public Function GetFirstValue(strFieldName As String, _
  15.                               strDataSetName As String) As Variant
  17.     Dim rs As New ADODB.Recordset
  19.     With rs
  20.         .CursorType = adOpenForwardOnly
  21.         .LockType = adLockReadOnly
  22.         .ActiveConnection = CurrentProject.Connection
  23.         .Open strDataSetName
  24.         GetFirstValue = .Fields(strFieldName)
  25.         .Close
  26.     End With
  28.     Set rs = Nothing
  30. End Function

Let us say the table has name [tblTable].
The first query ([qrySorted]) just sorts the table by some field:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTable.*
  2. FROM tblTable
  3. ORDER BY tblTable.[Some field];
The second query ([qryEnumerated]) returns enumerated records of [qrySorted].
Expand|Select|Wrap|Line Numbers
  1. SELECT qrySorted.*, RecordNumber(qrySorted.keyID, GetFirstValue("keyID","qrySorted")) AS lngRecordNumber
  2. FROM qrySorted;

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

Mar 1 '08 #2

P: 3
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.
Mar 1 '08 #3

P: 3
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?
Mar 1 '08 #4

Expert 2.5K+
P: 2,653
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?
Mar 1 '08 #5

Expert Mod 15k+
P: 31,770
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.
Mar 7 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.