Solved

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

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

 
LVL 47

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 47

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 47

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 47

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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