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).O penDatabase(Tex t10) ' Text10 contains the path
to the Access MDB
Set Rs2 = Db2.OpenRecords et("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 +27824170857 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.OutBuffer Count = 0
Comm1.InBufferC ount = 0
okout = ""
Image1.Visible = Not Image1.Visible
If imgNotConnected .Visible = False Then
imgConnected.Vi sible = Not imgConnected.Vi sible
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.OutBuffer Count = 0
Comm1.InBufferC ount = 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.OutBuffer Count = 0
Timer3.Interval = 1000
Exit Sub
End If
Wend
Comm1.OutBuffer Count = 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é]

Re: Strange, strange...

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

"HKSHK" <hkshk@gmx.ne t> wrote in message
news:37464b94.0 307281927.479c9 97a@posting.goo gle.com...[color=blue]
> Hello Bernard,
>[color=green]
> > 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[/color][/color]
what[color=blue][color=green]
> > I was expecting.
> > But, but....
> > When running the code, it looks like the records in Access are not[/color][/color]
updated[color=blue][color=green]
> > quickly enough and, therefore, the SMS are sent more than once (up to 10
> > times for some).[/color]
>
> I guess you encounter the same problem what I posted under " How can I
> flush File Buffers for an Access database?"
>[/color]
(http://groups.google.com/groups?q=hk...e=UTF-8&oe=UTF
-8&scoring=d&sel m=37464b94.0307 212226.2eb4ccf% 40posting.googl e.com&rnum=1).[color=blue]
>
> 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[/color]

[the Wiz]

Re: Strange, strange...

"Bernard André" <bernan@bernan. net> wrote:
[color=blue]
>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).
>[/color]
[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]

Re: Strange, strange...

Bernard,

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

Regards,
Alexey