My records are being inserted in the MIDDLE of the recordset!

Hi there,

I've struggled with this issue for a couple of months now, and have altered my code several times in an effort to solve the problem, but to no avail.  So here I am. :-)

I have developed a fairly simple VB/Access application that is installed on an employee machine.  The employee enters data through the application - it goes into his local database, and then at the end of the day he uploads his records to a database on the server.

The whole process seems to work quite well the majority of the time.  However, about once or twice a week, something weird happens.  His new records end up going into the server database in the wrong order - for example, the order of IDs of the new records might be 1275, 1276, 1277, 1278, 1272, 1273, 1274. I want them to go into the server DB in the same order that they're in his local database - but it sometimes puts the last few records in the middle of the other new records.  I thought that the .AddNew method always added the records to the end of the recordset.  Do I need to .MoveLast before I add the new records?  What I can't understand is that it works fine 80% of the time!

Here is the code in my upload routine...I've removed some irrelevant code, so if there's a missing End If or something, that's the reason why. :-)

Does anyone have any ideas why my code sometimes inserts the new records into the middle of the recordset?  I've tried stepping through in code but it always works fine then - of course! :)

Thanks!!!!  Christy :-)

Private Sub mnuTUploadNew_Click()
Dim i As Integer
Dim bDone As Boolean
Dim iID As Integer

'upload all new records
'sets the ID of the new record
    If Not datServerDB.Recordset.EOF Then
        iID = datServerDB.Recordset("ID") + 1
        iID = 1
    End If

'refreshes datNew - the database that contains the ID of all the new records to be uploaded

    Do While Not datNew.Recordset.EOF
'take the ID from datNew, then go to the main table and find the rest of the info for the
'record that is being uploaded
        bDone = False
        Do While Not datAudit.Recordset.EOF
            If datAudit.Recordset("ID") = datNew.Recordset("ID") Then

                datServerDB.Recordset("ID") = iID
                datServerDB.Recordset("Date") = datRollAudit.Recordset("Date")
                datServerDB.Recordset("User") = datRollAudit.Recordset("User")
                datServerDB.Recordset("Roll") = datRollAudit.Recordset("Roll")
                datServerDB.Recordset("DocType") = datRollAudit.Recordset("DocType")
                datServerDB.Recordset("Municipality") = datRollAudit.Recordset("Municipality")
                datServerDB.Recordset("Comments") = datRollAudit.Recordset("Comments")
                iID = iID + 1
                bDone = True
                If Not datNew.Recordset.EOF Then datNew.Recordset.MoveNext
                Exit Do
            End If
    MsgBox "Your new records have been uploaded to the server. Your local database will now be refreshed with the records from the server.", vbOKCancel, "Upload Successful!"
    Call RefreshRecords

End Sub

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is no guaranteed order of a the records in a table. Use an index to order them.
christy2931Author Commented:

You mean index a particular field?  I just want them to go in the server table in the same order they are in the local table...I'm not sure how to do that with an index.

That is to say that the order in which they are added is not guaranteed. If you want to see them in a particular order then you can create a recordset Ordered by this field (ID) is your case. It would be a good idea for the field to be an index. This would help with performance.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

My previous comment crossed with yours. I'm going off line now until tomorrow, so I'll just add that the ID field sounds as if it should be made the Primary Key since this is, by definition, unique (no duplicates allowed)
christy2931Author Commented:

Graham, I already have the ID field as the primary key.  I'm going to try using the requery method now.  Problem is, I won't know if it worked for at least a week or two - since it only happens sometimes!  I'll try it out and see what happens...

Thanks for your help!
>I want them to go into the server DB in the same order that
>they're in his local database

You can't do this.  The database engine determines the physical order.

There is no need to have the database in a particular order.  Anything you wish to do to the records in a certain order can be accomplished by using an index and processing the records in indexed order.

If you think this is the answer, give the points to GrahamSkan, he said it first, I just expanded on what he said.
christy2931Author Commented:

Thanks, everyone.  My ID field is the primary key, and it's indexed as well.

How do you mean processing the records in indexed order?  I think I already do that - I take the records in the local database, and add them one at a time to the server database - I think I've written pretty straightforward code to do this.

I don't have IDs in the local table - well, I do, but I don't give the record the same ID in the server table.  It just goes to the last record, gets the ID, adds 1 to it, and that's the ID for the next record.  This is to make the application multi-user.

Problem is I end up with the IDs in the right order, but the records in the wrong order.  I need the records to be in the right order because one of the fields is date/time entered, and I need the time to be in chronological order.  So that when the user is scrolling through the records using arrows on the form, they are in the right order of id AND time.  This doesn't happen when the database doesn't keep the same order as the local table.

Instead, my database might look like this.

ID                Date  

1250      12/01/2003 2:35:28 PM
1251      12/01/2003 2:36:17 PM
1252      12/01/2003 2:36:59 PM
1253      12/01/2003 2:40:24 PM
1254      12/01/2003 2:41:11 PM
1255      12/01/2003 2:41:58 PM
1256      12/01/2003 2:37:34 PM
1257      12/01/2003 2:38:04 PM

Records 1253, 1254, and 1255 should be at the end, instead of in the middle.

Are you saying there's not really a way to ensure this happens?  GULP! :-)


>How do you mean processing the records in indexed order?  
>I think I already do that - I take the records in the local database,
>and add them one at a time to the server database - I think I've
>written pretty straightforward code to do this.

Indexing or ordering before the records are added to the table is not what we were referring to.  Read on ...

>I need the records to be in the right order because one of
>the fields is date/time entered, and I need the time to be
>in chronological order.  

Given your sample table of records, to see them in the proper order you would use SQL such as:
FROM tableName

This will run faster if the table has an index for the ID field and an index for the Date field.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
christy2931Author Commented:

So you mean they might be out of order in the actual table, but to display them I would use the SQL code - they would then be displayed on the form in the correct order?

I could try that - I would really like them to be in the correct order in the actual table, but that might be a pipe dream I guess. :-)

Why is it that the database engine does that sometimes and not other times?  It seems straightforward - I take the first record, add it to the server and call the update method to save it.  Then I take the second record, add it to the server, and call the update method.  I don't understand how it gets messed up???

Argh.  Thanks for the tips. :-)

what we ae trying to tell you is that PHYSICAL order is almost meaingless in a relational database.  In fact, you have ALMOST no control over physical order.  What you CAN control is the order by which records ar DISPLAYED, or RETRIEVED.  tht is what the Order By clasuse in te SQL is for.  If you want to see the records in the order that they were added to the database  (and assuming that you hve a DateAdded Field - note the name should be DateAdded and NOT Date, which is a reserved word, and should NEVER be used as the name of a field), then you would use SQL that included the Order By DateAdded clause.

If Order By DateAdded  (or Order By DateAdded ASC) you will see that records form OLDEST to NEWEST (oldest first, newest last) (ASC means Ascending, and ASC is the defauly)

if Order By DateAdded DESC - you will see NEWEST first and OLDEST Last.

Your choice.

> I don't understand how it gets messed up???
Neither do I.  My guess is that the database optimizes the inserts.  Sometimes the optimization results in the physical order you wish, sometimes it doesn't.
Morning Christy,

I have looked at your further explanation and I now understand that you are not concerned with the physical order of the records, but with matching the ID and date.

I've looked at your first posting in detail and cannot see exactly how you are trying to ensure that they match. You actually set the server ID to the next available, dropping the ID of the original recordset.

Why don't you just copy the ID across?

christy2931Author Commented:

Good morning Graham. :-)

Thanks for your comments.  Yes, you're right, I basically want to ensure that the ID and the date value both increase with each record - this is how it happens in the local database.  THe reason I need this to happen is that I have other fields (I removed them when I posted the code for length purposes!) that increase.  Basically the data is on rolls of microfilm - each document record has a roll number, a beginning frame number, and an ending frame number.  These values increase with each record, so it's important that the records be in the right order, i.e. the order the user enters them.

It would be great if I could just copy the ID across - the reason I don't is to make the application multi-user.  I had hoped that multiple users could be entering records, and when they upload at different times, it would just assign the next ID to the record wqhen it goes into the server db.

I think I've decided that I don't care if the records are in the right order.  Like everybody has told me, I can always use SQL to sort any way I need to.  As long as the IDs are in order, which they always are, I will be happy. :-)

Thanks to everyone for your replies!  Since I got so many good replies, I will read back through them and split the points accordingly.  Thank you again!

If each roll of Microfilm, and each frame within the roll, has a Unique identifier - Roll Number and Frame Number, in combinaton, is unique, then you should simply use THAT combination and the primary Key of your records, or at least, create a Unique Index, in the Table, on that combination.  Then you caneasily retrieve all the frames for a give roll, without being concerned about the other fields.  If the records are ALWAYS entered with all the frames for a selected roll being entered and the same time (same session), then you have NOTHING to worry about. And since  each roll is identified by its specific roll number, then there is no problem with 'multi-user' entry, since each user is entering a different roll, hence a different roll number.

christy2931Author Commented:

Thanks, everyone!  I have used an SQL statement to populate my recordset, so that's it's always in the correct order.  It seems to be working okay so far!

I am having the exact same problem.  I have several programs that read through a database in date order and perform a complex set of calculations that do not work properly if the records are out of order.  I can retreive each record, check the date, and if the date is wrong then proceed to read through the database searching for the next date in sequence - however that process is way too slow.  Even if the records are properly indexed they do not read in the indexed order (they display in the indexed order but when a VB program opens the database and moves to the top the next record read is not necessarily the next record).

In fact the "Movenext" command has virtually no meaning  - indexed or not.

Furthermore, if I import a comma delimited textfile the records are often placed out of order.

It doesn't make any sense.
If the comments in this question don't help you to fix your problem, I suggest that you ask one of your own. The only experts likely to find this are the original contributors.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.