[RESOLVED] How to prevent VB6 from hanging while refreshing remote database?

[IHappenToBe']

Hi everyone.

I have this big all-right program, and I want to add to it an ADODC that reads from a remote database (throught a DSN with ODBC) every 5 seconds for read for messages from me or other users, like a chat.
It's all fine, but every time I do Adodc1.Refresh, the programs hangs for 1 second or so, and it can be annoying if you're typing or something.

So, how can avoid that little delay, so the control can work in background without freezing everything else?
I'm using an Adodc1, not by code but design-created, whose only ConnectionString is DSN = MyDsn.
Every time I do:

AdoDc1.RecordSource = "SELECT * FROM messages where read = 0"
AdoDc1.Refresh

if freezes for 1 second or so. (Works perfectly, the only problem is the one I mentioned.)

Thank you.

[Niya]

I assume its causing the UI to stutter ? If so, then multi-threading is what you need but unfortunately this cannot be done in VB6 without some headache. However, I believe that ActiveX EXEs were provided as a way to work multi-threading into VB6 apps so I'd look that up. If you can pawn that query off on another thread through an ActiveX EXE, that might suffice.

[Niya]

Wait a second.....Are you seriously using a database to create a chat client ? Is that the primary purpose of your database ?

[IHappenToBe']

If you can pawn that query off on another thread through an ActiveX EXE, that might suffice.

Ok, I'll try. I've never created an Active EXE, but I'll try.

How many records is that query returning anyway ?

It doesn't matter. 1 or 100, it's the same delay. About 1 second. I've try using code, it's the same thing... well, more delay, actually.

[IHappenToBe']

Wait a second.....Are you seriously using a database to create a chat client ? Is that the primary purpose of your database ?

Well, I'm not proud of it, but it's not a teenagers chat, it's like a really slow chat, more like messages, so it's ok.
And I searched and tried a lot to make a proper chat using me as Server, the problems are:
1) I -Server- have to be always logged on
2) I don't have a static IP and I'm behind a router, so those chats only works on LAN connections

[Doogle]

Using ADO you can create an Asynchronous Connection to the Database - see here: http://support.microsoft.com/kb/194960

[IHappenToBe']

Using ADO you can create an Asynchronous Connection to the Database - see here: http://support.microsoft.com/kb/194960

I'm using VB6, that's not VB6.

[IHappenToBe']

Well, I've found this: http://www.codeproject.com/Articles/...-walkthrough-w
about making asynchronous processes in VB6. I'll explore that.

[Doogle]

It's not too difficult.

Define the Connection Object WithEvents, create the connection string and Open the connection with the adAsyncConnect Option e.g.

Dim WithEvents db As Connection

  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Public\HairDesign.mdb;", , , adAsyncConnect

When the connection has been established the db_ConnectComplete event triggers. In this routine you can set a global variable to indicate that the connection has been made.
You can then execute some sort of query. When the query has completed the db_ExecuteComplete event triggers and in that routine you can process the results of the query (e.g. Update some control or other with the results)

[IHappenToBe']

It's not too difficult.

Define the Connection Object WithEvents, create the connection string and Open the connection with the adAsyncConnect Option e.g.

Dim WithEvents db As Connection

  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Public\HairDesign.mdb;", , , adAsyncConnect

When the connection has been established the db_ConnectComplete event triggers. In this routine you can set a global variable to indicate that the connection has been made.
You can then execute some sort of query. When the query has completed the db_ExecuteComplete event triggers and in that routine you can process the results of the query (e.g. Update some control or other with the results)

Thank you, Doogle! That was EXACTLY what I needed. It doesn't hang anymore.
Thank you very very much

[Doogle]

This is how it might hang together

Private WithEvents db As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub db_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
Timer1.Enabled = True
End Sub

Private Sub db_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Dim intI As Integer
If Not (rs.EOF And rs.BOF) Then
    Do
        For intI = 0 To rs.Fields.Count - 1
            Debug.Print rs.Fields(intI).Name, rs.Fields(intI).Value
        Next intI
        rs.MoveNext
    Loop Until rs.EOF
Else
    Debug.Print "No records returned from Query"
End If
rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Load()
Timer1.Enabled = False
Timer1.Interval = 5000
Set db = New ADODB.Connection
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Public\HairDesign.mdb;", , , adAsyncConnect
End Sub

Private Sub Timer1_Timer()
Dim strSQL As String
strSQL = "SELECT * FROM Appointments"
Set rs = New ADODB.Recordset
rs.Open strSQL, db, adOpenStatic, adLockOptimistic
End Sub

Change the connection string to whatever you need and keep the adAsyncConnect option
Change the Table name to whatever you need

When you run, the timer will be enabled once the connection is established and every 5 seconds it will query the Table and when the results are available will display them in the Immediate Window. This all happens asynchronously whilst your program may be doing other things.

EDIT: I see you beat me to it.