Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 3rd, 2004, 11:45 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO + Open Table

I have a table that is sorted ASC by three columns: CPT4, MOD and EFFDATE in that order. So, of course it sorts by CPT4 first, MOD second and EFFDATE third.

There are multiple rows for each CPT4 and there are different dates for each CPT4. I am going through the table one record at a time and adding an entry in the 'flag' column to tell if the record does not have the most current date for the CPT4.

Problem: When the ADO recordset is navigating through the table it does not keep my sort that I did in the table. This is a problem because my whole code depends upon these three columns being sorted the way they are.

Here is my function that I am using:

Function SortOutVANTAG05()
    Dim rst As New ADODB.Recordset

    Dim First As Date
    Dim Second As Date

    rst.Open "SELECT * FROM VANTAG05", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

    Second = #1/1/2005#

    Do Until rst.EOF

        First = rst!EFFDATE

        If First > Second Then

            rst.MovePrevious
            rst!flag = "Not the Current One"
            rst.MoveNext

        End If

        Second = First

        rst.MoveNext

    Loop

End Function

Thanks for the help.

 
Old February 3rd, 2004, 11:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Just change your SQL so that the recordset is sorted in the way you want:
SELECT * FROM VANTAG05 ORDER BY CPT4, MOD, EFFDATE
 
Old February 3rd, 2004, 12:16 PM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Perfect.

Thanks a lot.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO recordset to table kanoorani VB Databases Basics 3 May 16th, 2006 02:59 AM
problem using ADO to open form in MS Access apike VBScript 3 May 2nd, 2006 06:25 AM
Create table from another table using ADO Kaustav Pro VB Databases 0 October 15th, 2005 05:44 AM
Backup an open access database (ado) jolzy VB Databases Basics 0 October 27th, 2004 09:38 AM
ADO connecting to a Paradox 8.0 table cpopham Access VBA 0 December 5th, 2003 01:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.