?
Solved

How to use ROW_NUMBER() SQL function is a VB6 SQL Query

Posted on 2014-10-19
11
Medium Priority
?
582 Views
Last Modified: 2014-10-20
I have a VB6 app querying an Access DB and need to create a query that generate a row number reference on each record in the query. I have tried using:

sSQL = "SELECT ROW_NUMBER() OVER (ORDER BY tblOperators.OperatorID) AS row_id, tblOperators.* FROM tblOperators"

but I get a syntax error (missing operator) each time! I can't see what I have got wrong with this SQL text! Perhaps there is a Reference I need in my project?
I feel a little dumb!
0
Comment
Question by:mortonkr
[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
  • 4
  • 4
  • 2
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40391336
Access does not support the row_number() function [or the over() clause]

both of those are supported by SQL Server and Oracle (for example) but not to my knowledge by Access
0
 

Author Comment

by:mortonkr
ID: 40391338
OK, thanks for that Paul. Can you help me with the SQL syntax to achieve the same result in Access?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40391396
afraid not, I'm not that strong with Access. I will add the Access topic to this question which should attract the right skills.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40391600
Can't you use the VB6 AbsolutePosition's recordset property?
Then your select will be:
sSQL = "SELECT * FROM tblOperators ORDER BY OperatorID"

Open in new window

0
 

Author Comment

by:mortonkr
ID: 40391742
What I am trying to do is to generate a fresh row reference for an ever changing list of users with their unique ID. I ma doing this as I need to bring this value out through a subsequent query whose result I use to allocate a colour to the user for graphing.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40391751
It's only a different way to do it. I think the only thing you need to change is how to reference the rowID.
YourRecordset.AbsolutePosition instead of YourRecordset.Fields(0)
0
 

Author Comment

by:mortonkr
ID: 40391759
Forgive my ignorance Vitor but I don't see how this method will provide a rowID field in each record which I can then read into a further query which has each user referenced a number of times. Can you help with this?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40391777
Since MS Access doesn't have the functionality that you want I only pointed you to an alternative.
ADO Recordset has an attribute that is the AbsolutePosition that provides a distinct number for each record retrieved and I think it's that you need, right?

You can run a small test to check if this can be an acceptable workaround to you.
0
 

Author Comment

by:mortonkr
ID: 40391796
Do you know if it is possible to create a temporary field in a table and fill it with that AbsolutePosition value?
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1500 total points
ID: 40391802
My time as VB6 developer already gone but as far as I remember you can add new fields in a recordset with the .Append method and then you can update that field with the .AbsolutePosition property.
But as I said the best think is to do some tests to check if the results it's what are you looking for.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Make the most of your online learning experience.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Six Sigma Control Plans
Suggested Courses

764 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