VB 6.0 - Dynamic Cursor Query - Asked By Dhrubajyoti Dey on 19-Jul-14 02:18 AM

 I have created a simple applicaion with adodb connection and a recordset consists of just 3 columns.  I used the cursor type as dynamic, cursor location as server, backend as Access. This application is running from two different machines, while sharing the same database. When I add one row to the database from one machine, the other machines cursor  is not getting the new record until and unless this crsor is refreshed. But according to the theory of Dynamic cursor it should get it. What is happening here? Is it a problem of Access, or anything is missed out by me.

...so I don't have a good reference point to start from.
Post your code to the forum.  

I don't think Access can share cursors like this across machines even though it's a server side recordset cursor.  I'd try the same code in a couple of test scripts on the same machine and see what happens before persuing this further.

I looked up Dynamic cursors and it does suggest that what you are trying to do "should" work.   

On a side note, there are quite a few other people visiting the forums now.  You don't necessarily have to address these to me.

Here is the sample code. Let's start this program from two different machines M1 and M2. In M1 click cmdSave button and then click cmdNewRec button in M2. According to the theory of Dynamic cursor M2 should get newly inserted record. Please check it. I haven't tried in other rdbms (like Oracle, SQL Server). So check in those databases too.

'**********************************
Option Explicit

Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdNewRec_Click()
    rs.MoveLast
    MsgBox rs.Fields("CategoryName")
End Sub

Private Sub cmdSave_Click()
    conn.BeginTrans
    conn.Execute "INSERT INTO CATEGORIES (CATEGORYNAME, DESCRIPTION) VALUES('PRABAL','DD')"
    conn.CommitTrans
End Sub

Private Sub Form_Load()
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SharedFolder\NWind.mdb"
    
    With rs
        .CursorLocation = adUseServer
        .LockType = adLockOptimistic
        .CursorType = adOpenDynamic
        Set .ActiveConnection = conn
        .Open "SELECT * FROM CATEGORIES ORDER BY CATEGORYNAME", , , , adCmdText
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

...with two separate instances of your VB app running on the same PC?

I got even more surprising result. It's not working for two separate instances of VB on same machine. It's not working for the same application also!!! It's simply not showing the inserted record until and unless I refresh the recordset. What's happening?

...Today is kind of a busy day.  Wish I could be of more help.