Solved

Accessing access database in descending order

Posted on 2000-04-29
12
167 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now