Solved

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

Posted on 2003-12-01
18
354 Views
Last Modified: 2010-05-01
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
    datServerDB.Refresh
   
'sets the ID of the new record
    If Not datServerDB.Recordset.EOF Then
        datServerDB.Recordset.MoveLast
        iID = datServerDB.Recordset("ID") + 1
    Else
        iID = 1
    End If

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

    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
        datAudit.Refresh
        datAudit.Recordset.MoveFirst
        bDone = False
       
        Do While Not datAudit.Recordset.EOF
       
            If datAudit.Recordset("ID") = datNew.Recordset("ID") Then

                datServerDB.Refresh
                datServerDB.Recordset.AddNew
                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")
                datServerDB.Recordset.Update
                iID = iID + 1
                datNew.Recordset.Delete
                bDone = True
                If Not datNew.Recordset.EOF Then datNew.Recordset.MoveNext
                Exit Do
            End If
            datRollAudit.Recordset.MoveNext
        Loop
   
        datRollAudit.Recordset.MoveNext
    Loop
   
    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
---------------------------------------------------------------------------------------------------------


0
Comment
Question by:christy2931
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 9850615
There is no guaranteed order of a the records in a table. Use an index to order them.
0
 

Author Comment

by:christy2931
ID: 9850653

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.

Christy
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 9850660
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 9850709
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)
0
 
LVL 5

Assisted Solution

by:peterpuscas
peterpuscas earned 25 total points
ID: 9850741
try
       datServerDB.Recordset.Requery
after
       datServerDB.Recordset.Update
0
 

Author Comment

by:christy2931
ID: 9851239

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!
Christy
:-)
0
 
LVL 9

Expert Comment

by:dancebert
ID: 9851662
>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.
0
 

Author Comment

by:christy2931
ID: 9851854

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! :-)

Thanks!!!
Christy

0
 
LVL 9

Accepted Solution

by:
dancebert earned 75 total points
ID: 9851977
>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:
SELECT Id, Date
FROM tableName
ORDER BY Id, Date

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





0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:christy2931
ID: 9852085

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. :-)

Christy
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 75 total points
ID: 9852290
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.

AW
0
 
LVL 9

Expert Comment

by:dancebert
ID: 9852426
> 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.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 75 total points
ID: 9856342
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?

Graham
0
 

Author Comment

by:christy2931
ID: 9857497

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!

Christy
:-)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9861483
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.

AW
0
 

Author Comment

by:christy2931
ID: 9895861

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!

Thanks,
Christy
:-)
0
 

Expert Comment

by:jaugen
ID: 12127866
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12127952
jaugen,
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 37
using Access 8 52
Access query that references subform 5 35
Passing a Text Box name to a Sub 6 18
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 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

23 Experts available now in Live!

Get 1:1 Help Now