Solved

How to get rownum in Access table

Posted on 2002-04-27
6
16,871 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
[X]
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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

622 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