Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Accessing access database in descending order

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
asd987
Asked:
asd987
  • 6
  • 3
  • 2
  • +1
1 Solution
 
MarineCommented:
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
 
BabyFaceCommented:
Hi,

Try an SQL statement like this:
SELECT TOP 10 * FROM discussions ORDER BY number DESC
0
 
MarineCommented:
opps sory i messed up the the position there. The credit should still be mine.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
asd987Author Commented:
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
 
MarineCommented:
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
 
asd987Author Commented:
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
 
AzraSoundCommented:
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
 
AzraSoundCommented:
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
 
MarineCommented:
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
 
MarineCommented:
change this line
Set rs = lMyDB.OpenRecordset("sSql", dbOpenDynaset)

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

0
 
asd987Author Commented:
Thanks
0
 
MarineCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now