• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

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

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
mortonkr
Asked:
mortonkr
  • 4
  • 4
  • 2
1 Solution
 
PortletPaulCommented:
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
 
mortonkrAuthor Commented:
OK, thanks for that Paul. Can you help me with the SQL syntax to achieve the same result in Access?
0
 
PortletPaulCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
mortonkrAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
mortonkrAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
mortonkrAuthor Commented:
Do you know if it is possible to create a temporary field in a table and fill it with that AbsolutePosition value?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now