strange recordset behaviour: records not sequential

[Glade]

I'm working with an MS Access 2000 database in Visual Basic. I've created some data objects that deal with the different tables in the database. I just finished porting all of the data in an old 97 database to the new and improved 2000 database. So far everything seems to have worked fine, except for one strange problem.

There are 408 records in the main table of the 2000 database. I'm using autonumber for their key field, the first record has key 30, the last has key 437. The strange thing is that my data access object does not read them in sequential order.

To be more specific, I have created a standard ADO recordset object that deals with the main table. When I use the MoveFirst method of that object, it brings me to record number 95, instead of 30. I originally thought this meant it was ignoring the first 65 records, but I have just discovered that for some reason using the MoveNext method to go through each record goes in sequential order from 95 to 234, then it goes from 30 to 94, then takes up again where it left off at 235 and continues properly until the end of the recordset.

So the recordset perceives the table keys as going like this:

95, 96, 97...234, 30, 31, 32, 33...94, 235, 236, 237...437

Anyone know have any idea why this is occurring? I've made sure that the key is sorted in ascending order, and when opened in MS Access the table displays exactly as it should.

Any help would be appreciated.

-Adrian

 

[TudorSmith]

Wierd!

Have you tried forcing the recordset to jump to the last record then back to the first record before starting, just incase the system has some sort of bookmark or flag in place.

I often make the records do this:

rst.MoveLast
rst.MoveFirst
do until rst.EOF
  [Code Detail]
  rst.MoveNext
loop

It may help? birklea ~©¿©~ <><
&quot;If at first you succeed, try and remember how you did it!&quot;

 

[Glade]

^^^ Good suggestion, but it doesn't work.

Just to be sure that it wasn't an extraneous problem due to the design of some other object in my program, I wrote the following debug routine to check my data:

-----------------------------

Public Sub Display_Items()
'just a debug tool, display the items 30 at a time so i can 
'figure out what is wrong with the order

    Dim i As Integer
    Dim message As String

    m_recMain_Items.MoveLast
    m_recMain_Items.MoveFirst
    i = 0
    
    While m_recMain_Items.EOF = False
    
        message = message & vbCr & m_recMain_Items.Fields("Item") & ": " & m_recMain_Items.Fields("Name")
        
        If i > 30 Then
        
            MsgBox message
            message = ""
            i = 0
            
        End If
        
        m_recMain_Items.MoveNext
        
        i = i + 1
        
    Wend

End Sub

-----------------------------

So this displays chunks of 30 records at a time: and the strange behaviour I mentioned in my first post still happens.

Anyone else?

-Ade

 

[Glade]

Okay, I've made some progress with this problem.

The problem is caused by this line, which is inside my database connection routine:

g_adoConnect.CursorLocation = adUseClient

If I comment out this line, the order is as it should be. HOWEVER, the new problem is this: if I don't set the CursorLocation property, I can no longer access the AbsolutePosition property for some reason. In order to accomplish a data-mining task, I need to be able to access that property. Once the above line of code is commented out, AbsolutePosition returns -1.

Suggestions? I will write my own AbsolutePosition sub if I can't get around this soon.

-Ade