Solved

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

Posted on 2014-10-19
11
562 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
  • 4
  • 4
  • 2
11 Comments
 
LVL 48

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 48

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

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 49

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 49

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 49

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
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?
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

749 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