Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to get rownum in Access table

Posted on 2002-04-27
6
Medium Priority
?
17,078 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Jim.
0
 
LVL 58
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

971 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