Solved

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

Posted on 2014-10-19
11
535 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
 
LVL 46

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now