Solved

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

Posted on 2014-10-19
11
572 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
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.

 
LVL 50

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 50

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 50

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 50

Accepted Solution

by:
Vitor Montalvão earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

724 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