Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Accessing access database in descending order

Posted on 2000-04-29
12
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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