Strange, strange...

[Bernard André]

Hi All,
I posted this already today and I am really getting bananas with this issue:

Using Visual Basic 6.0, I am sending SMS. These SMS can be added to an
Access 2000 table by users on the network.
Everything works well when executing the code step by step (F8).
I can trace this in the Access table, that reacts exactly according to what
I was expecting.
But, but....
When running the code, it looks like the records in Access are not updated
quickly enough and, therefore, the SMS are sent more than once (up to 10
times for some).

Here is how I open the table in the Access MDB:

Dim Db2 As Database, Rs2 As Recordset
Set Db2 = Workspaces(0).OpenDatabase(Text10) ' Text10 contains the path
to the Access MDB
Set Rs2 = Db2.OpenRecordset("pendingsms")
Set Data2.Recordset = Rs2
Data2.Recordset.Index = "ID"

It opens correctly and I can view SMS added by net users, using a DbGrid
linked to Data2.
When I stop the timer3 procedure, I can see, in that same DbGrid, the
records marked as sent, after a few seconds.
I have a timer3 on the form, to scan the pending SMS and send them. Mainly,
I browse the Access table and for each record where the field "sent" is
FALSE, I sned the SMS and, if receiving an OK from the modem, I change the
"sent" field to TRUE.
ID tonum texte Sent
24904 [phone] test open/close/open Yes


Here is the code associated to Timer3

Private Sub Timer3_Timer()
Timer3.Interval = 0
Dim erlop As Integer, nbrecs As Integer, X As Integer
Dim Db2 As Database, Rs2 As Recordset
erlop = 0
Comm1.OutBufferCount = 0
Comm1.InBufferCount = 0
okout = ""
Image1.Visible = Not Image1.Visible
If imgNotConnected.Visible = False Then
imgConnected.Visible = Not imgConnected.Visible
End If
If Comm1.PortOpen = True Then
If Not Data2.Recordset.EOF Then
Data2.Recordset.MoveFirst
While Not Data2.Recordset.EOF
If Data2.Recordset("Sent") = False Then
okout = ""
erlop = 0
Comm1.OutBufferCount = 0
Comm1.InBufferCount = 0
Comm1.Output = "at+cmgs=" & Chr(34) &
Data2.Recordset("tonum") & Chr(34) & vbCr & Data2.Recordset("texte") &
Chr(26)
While InStr(1, okout, "OK") = 0 And InStr(1, okout, "ERROR")
= 0
erlop = erlop + 1
okout = okout & Comm1.Input
dummy = DoEvents()
If erlop > 500 Then
erlop = 0
Comm1.OutBufferCount = 0
Timer3.Interval = 1000
Exit Sub
End If
Wend
Comm1.OutBufferCount = 0
If InStr(1, okout, "OK") <> 0 Then
Data2.Recordset.Edit
Data2.Recordset("Sent") = True
Data2.Recordset.Update
Data2.Recordset.Requery
Data2.Recordset.Delete
wait (3)
End If
Else
Data2.Recordset.Delete
Data2.Refresh
End If
Wend
End If
End If
Timer3.Interval = 1000
End Sub

What am I doing wrong?
If someone can help, I would be grateful.
Regards,
Bernard

[Bernard André]

Hello HKSHK,

thanks for your reply.

I received an answer from Peter Russel, in the comp.databases.ms-access
newsgroups, telling me to have a look at

http://support.microsoft.com/default.aspx?scid=kb;en-us;248833

This is an interesting article about our common problem.

Hope it will help you too.

Regards,

Bernard

Hello Bernard,

Post by Bernard André
Using Visual Basic 6.0, I am sending SMS. These SMS can be added to an
Access 2000 table by users on the network.
Everything works well when executing the code step by step (F8).
I can trace this in the Access table, that reacts exactly according to what
I was expecting.
But, but....
When running the code, it looks like the records in Access are not updated
quickly enough and, therefore, the SMS are sent more than once (up to 10
times for some).

I guess you encounter the same problem what I posted under " How can I
flush File Buffers for an Access database?"

(http://groups.google.com/groups?q=hkshk%40gmx.net&hl=en&lr=&ie=UTF-8&oe=UTF
-8&scoring=d&selm=37464b94.0307212226.2eb4ccf%40posting.google.com&rnum=1).

I assume that it is Windows which has the database in its writing
cache. It might take up to 5 seconds before the changes are written
and can be requeried. Unfortunately I haven't found a solution for
this problem myself.
Kind Regards,
HKSHK

[the Wiz]

Post by Bernard André
Hi All,
Using Visual Basic 6.0, I am sending SMS. These SMS can be added to an
Access 2000 table by users on the network.
Everything works well when executing the code step by step (F8).
I can trace this in the Access table, that reacts exactly according to what
I was expecting.
But, but....
When running the code, it looks like the records in Access are not updated
quickly enough and, therefore, the SMS are sent more than once (up to 10
times for some).

[snip]

If you're using a version of Windows that provides control of write buffering,
turn it off.

If you have a LOT of RAM, put the database in a RAMdisk (fast - but fragile).

Find a better database/platform.

More about me: http://thelabwiz.home.mindspring.com/
VB3 source code: http://thelabwiz.home.mindspring.com/vbsource.html
VB6 source code: http://thelabwiz.home.mindspring.com/vb6source.html
VB6 - MySQL how to: http://thelabwiz.home.mindspring.com/mysql.html
My newest language - NSBasic for the Palm PDA: http://thelabwiz.home.mindspring.com/nsbsource.html
Drivers for Pablo graphics tablet and JamCam cameras: http://home.earthlink.net/~mwbt/
johnecarter atat mindspring dotdot com. Fix the obvious to reply by email.

[Alexey Shepetko]

Bernard,

Try using queries for "select" and "update" operations and you'll get
results always up-to-date.

Regards,
Alexey