Solved

Accessing access database in descending order

Posted on 2000-04-29
12
190 Views
Last Modified: 2013-11-23
Hi,

I have a table in a database and I want to access the latest 10 records which were added.

Table name : discussions
Fields :
number - incremental number
headline - text
article - memo
numitems - integer

The number field increments for each record that is added and say there are 100 records I would like to retrieve record 100 down to 91 in that order using the 'number' field to find out the latest/highest records.

I would like to get this information into variables inside a VB6 program so I can process it within my program.

Any ideas anyone ?

Thanks

0
Comment
Question by:asd987
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2762092
ok if you want to have the top 10 records then this works
SELECT * TOP 10 FROM tablename order by fieldname asc

if you want the buttom 10 then do this

SELECT * TOP 10 FROM tablename order by fieldname desc
0
 
LVL 1

Expert Comment

by:BabyFace
ID: 2762109
Hi,

Try an SQL statement like this:
SELECT TOP 10 * FROM discussions ORDER BY number DESC
0
 
LVL 6

Expert Comment

by:Marine
ID: 2762115
opps sory i messed up the the position there. The credit should still be mine.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:asd987
ID: 2762116
Hi,

I don't know much about database access and have no idea how to get this into a recordset and then into variables.

Any further comments ?

Thanks
0
 
LVL 6

Expert Comment

by:Marine
ID: 2762123
which value of a recordset do you want to have the value of ?

Ok

I assume that connection is done and everythins is correct there.
sSql = "SELECT TOP 10 * FROM discussions ORDER BY number DESC"
rs.Open sSql,Connection,3,3

now if you want to have a ceratain value of a variable there lets say we place it into a listbox the value of field 3
rs.MOveFirst
Do while not rs.EOF
   list1.additem rs(2)
   rs.Movenext
LOOP

Now you have all the values of field 3 in your listbox
0
 

Author Comment

by:asd987
ID: 2762204
Hi,

When I said I wasn't very familiar with access I meant it - I guessed how to open the database,m etc but I think I'm doing it wrong.

Here's what I've got and it doesn't work :
I get an error saying 'method or data member not found' and the 'open' part of rs.open is highlighted as the error.

Could you please post a working example which opens the database and closes it.


Dim lMyDB As Database
Dim rs As Recordset

Set lMyDB = DBEngine.OpenDatabase("c:\forums\forums.mdb")
Set rs = lMyDB.OpenRecordset("discussions", dbOpenDynaset)

sSql = "SELECT TOP 10 * FROM discussions ORDER BY number DESC"
rs.Open sSql, Connection, 3, 3

'now if you want to have a ceratain value of a variable there lets say we place it into a listbox
'the value of field 3
rs.MoveFirst

Do While Not rs.EOF
    'list1.AddItem rs(2)
    MsgBox rs(2)
    rs.MoveNext
Loop

0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2762215
First, go to the Project menu and select References, then check off the two items:
Microsoft Activex Data Objects 2.1 Library
Microsoft Data Binding Collection

Now paste this code into your form module:



Dim dbConn As Connection
Dim rs As Recordset



Private Sub Form_Load()
    Set dbConn = New Connection
    dbConn.CursorLocation = adUseClient
    dbConn.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = " & _
        "C:\yourpath\yourdatabase.mdb;"
    Set rs = New Recordset
    rs.Open "SELECT TOP 10 * FROM Discussions ORDER BY number DESC", dbConn, adOpenStatic, adLockOptimistic
End Sub

Private Sub Form_Unload(Cancel As Integer)
    dbConn.Close
    Set rs = Nothing
    Set dbConn = Nothing
End Sub


that is the basic method of opening/closing your database
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2762220
if you would like an idea of exactly what your recordset looks like, go to the Project menu and select Components and choose
Microsoft DataGrid Control

Then after opening the recordset from the code above paste this code (assuming you name the datagrid DataGrid1, which is the name it gets by default)

Set DataGrid1.DataSource = rs

0
 
LVL 6

Accepted Solution

by:
Marine earned 100 total points
ID: 2762236
Ok you are using DAO. If you don't have it yet there referenced , reference it now. go to Project/References and choose MICROSOT DAO 3.6

When that is done you should be set

Dim lMyDB As Database
Dim rs As Recordset

SetlMyDb = Opendatabase("pathtoyourdatabase")
sSql = "SELECT TOP 10 * FROM discussions ORDER BY number DESC"

Set rs = lMyDB.OpenRecordset("sSql", dbOpenDynaset)

'now if you want to have a ceratain value of a variable there lets say we place it into a listbox
'the value of field 3
rs.MoveFirst

Do While Not rs.EOF
    'list1.AddItem rs(2)
    MsgBox rs(2)
    rs.MoveNext
Loop
0
 
LVL 6

Expert Comment

by:Marine
ID: 2762238
change this line
Set rs = lMyDB.OpenRecordset("sSql", dbOpenDynaset)

to this
Set rs = lMyDB.OpenRecordset(sSql, dbOpenDynaset)

0
 

Author Comment

by:asd987
ID: 2762373
Thanks
0
 
LVL 6

Expert Comment

by:Marine
ID: 2762384
Your Welcome. Thanks for points. Happy programming :). Also you should think about ADO most applications are switching to it. AzraSound example used ADO.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

773 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