Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to get rownum in Access table

Posted on 2002-04-27
6
16,675 Views
Last Modified: 2009-11-23
In Oracle, i can retrieve the row number of selected records by using rownum.

Example:
Select rownum, id, name from MyTable;

But how can i retrieve the row number of a record in Ms-Access (Equivalent to rownum in Oracle)??

Thanks
0
Comment
Question by:siiking
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6974478
Hi Siiking,

Since access doesn't do row nums, you can try this approach

http://groups.google.com/groups?hl=nl&selm=zmiJ6.30756%24_f3.516398%40news20.bellglobal.com&rnum=5

as stated in that post if you don't want to go the SQL road then there are two other ways to go

-do it with an ADO recordset and some VB
-write your own function also in VB

HTH:O)Bruintje
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 6974485
a little addition, in a real relational database there are no row numbers because the records are created on a heap.

and i'm speculating here that Oracle also uses a function to order the records prior to returning the row numbers. this is what you're going to do in Access also through a SQL query or with your own function. the ADO recordset will return pages of records and you bring in a order in the resultset.

the fastest way to solve this is inserting a column with a num as field that's sequential, but for every deletion or insertion you've to rerow the column to adjust to the new order. problem with this approach is that a record can be number 4 in a query and number 3 in one three months later

so it all depends on what you need to do.

:O)Bruintje
0
 
LVL 15

Expert Comment

by:cquinn
ID: 6975304
Just add an autonumber column - you may get gaps though, as stated by bruintje, when you delete records
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57
ID: 6980373
If your talking about a DAO recordset, it's the AbsolutePosition Property.  If not, fill in some more details.

Jim.
0
 
LVL 57
ID: 6980378
Note that if you want a simple row number added to a query, that's easily done and you don't need an autonumber.

As I said, fill in some more details on what it is your doing.  There may be other options as well.

Jim.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6991603
that's my second C for the day, if you don't like the comment don't accept it as answer especially when other experts are commenting also and we get no feedback fom your side.......darn!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question