473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access 2007 export specified rows

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
5 6749
FishVal
2,653 Expert 2GB
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
  1.  
  2. Public Function RecordNumber(varUniqueField As Variant, _
  3.                              varFirstValue As Variant) As Long
  4.  
  5.     Static lngRecordNumber As Long
  6.  
  7.     If IsNull(varUniqueField) Or IsNull(varFirstValue) Then Exit Function
  8.     If varUniqueField = varFirstValue Then lngRecordNumber = 0
  9.     lngRecordNumber = lngRecordNumber + 1
  10.     RecordNumber = lngRecordNumber
  11.  
  12. End Function
  13.  
  14. Public Function GetFirstValue(strFieldName As String, _
  15.                               strDataSetName As String) As Variant
  16.  
  17.     Dim rs As New ADODB.Recordset
  18.  
  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
  27.  
  28.     Set rs = Nothing
  29.  
  30. End Function
  31.  
  32.  

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];
  4.  
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;
  3.  

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

Regards,
Fish
Mar 1 '08 #2
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
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
FishVal
2,653 Expert 2GB
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
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Janne Ruuttunen | last post by:
Hello DB2 people, I'm having problems exporting >= 250000 lobs to IXF files with the LOBSINFILE option, using a legacy DB2 2.1 system on Win NT. If I don't specify a path for the lobs,...
5
by: jsudo | last post by:
I have a Access database with a large amount of records (close to 500,000) that I would like to export to Excel. I found out that Excel has the capability of of about 65,000 rows so I know I...
8
by: Jerome Ranch | last post by:
Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables...
20
by: TC | last post by:
I need an automated procedure to copy data from an Access table to a SQL Server table. Speed is important. What is the recommended technique? I can export the data from Access, copy it via FTP,...
4
by: Luvin lunch | last post by:
Hi, I've developed a worklist system in Access and I plan to deploy four copies of it to the four users that need it. I said I'll be deploying copies because there is no shared network in the...
5
by: bhodgins | last post by:
Hi, I am new on here, and had a newbie question that I am stumped with. I am not new to access, but am new to VB. I am trying to export BLOBs from a field called photo to external jpeg files. I...
4
by: myemail.an | last post by:
Hi all, I use Access 2007 and have the following problems: when exporting banal select queries (either to Excel or to a csv file) I find that exporting often doesn't work and creates a file with...
8
by: Irene | last post by:
Hi, I have an MS Access Database with 1 Table containing about 2 million records in Unicode (diferent languages). I would like to export the Table to a Text file (CSV, Tab, etc.) Access...
10
by: rmurgia | last post by:
We have set up a function as listed below to display a custom toolbar used to export data from the report. The function is called from the open event of each report: Function CBShowPrint() ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.