4 Replies - 1177 Views - Last Post: 17 October 2019 - 11:51 AM Rate Topic: -----

#1 Luigiview   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-October 19

ADO Connection. Update database INFO in all Computers connected

Posted 16 October 2019 - 03:34 PM

Hello,
I am working with VB 6.

I have the need to share a database in a LAN environment.

I have a program that opens an access database using DAO, I am running a couple of programs in different computers, connected by LAN network, accessing this same database.

My issue comes as follows, if I edit any data in computer A, I should expect to see that change in computer B by the time I look for that record. (What I do is moving the pointer record backward and forward in order to refresh the data.

But that is not the case, I don´t see any changes in computer B, not until I restart the program, and (I guess) the connection is updated.

How could I see the changes in real time?

I guess something's gotta to do with the way I am accessing / Opening the database.

What Am I doing wrong? or What am I missing?

The code I have is as follows...
    Dim Conn As new ADODB.Connection
    Dim Customer As New ADODB.Recordset

    Set Conn = New ADODB.Connection
    Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\customers.mdb;"
    Conn.Open

    Customer.CursorLocation = adUseClient

   Customer.Open "select * FROM Customers", Conn, adOpenDynamic, adLockBatchOptimistic


I am doing some pretty simple changes in the DB like changing the name field in Computer A..
    Customers("name") = "JOHN"
    Customers.Update

:code:

At this time, I would expect to see this change in Computer B if I "refresh" the data.



if you need any more info., please let me know.

Thanks for your comments and help.

This post has been edited by modi123_1: 16 October 2019 - 04:23 PM
Reason for edit:: In the future please use the [code] tag button in the editor


Is This A Good Question/Topic? 0
  • +

Replies To: ADO Connection. Update database INFO in all Computers connected

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15728
  • View blog
  • Posts: 63,014
  • Joined: 12-June 08

Re: ADO Connection. Update database INFO in all Computers connected

Posted 16 October 2019 - 04:25 PM

So a few things. First, the required warning that Access is intended to be a single user local DB. Trying to make it multi use provides difficulties with table locking and concurrent data updates.

SEcond - that snippet doesn't really explain the life cycle of the connection. You should be opening a connection right before you need it, and closing it right after. Clean up any transactions you may have started or what not.

Do not keep the connection going for the life time of the app.
Was This Post Helpful? 0
  • +
  • -

#3 Luigiview   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-October 19

Re: ADO Connection. Update database INFO in all Computers connected

Posted 17 October 2019 - 09:03 AM

View Postmodi123_1, on 16 October 2019 - 04:25 PM, said:

So a few things. First, the required warning that Access is intended to be a single user local DB. Trying to make it multi use provides difficulties with table locking and concurrent data updates.

SEcond - that snippet doesn't really explain the life cycle of the connection. You should be opening a connection right before you need it, and closing it right after. Clean up any transactions you may have started or what not.

Do not keep the connection going for the life time of the app.


Thanks for your replay Modi, If not access, what database should I use instead?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15728
  • View blog
  • Posts: 63,014
  • Joined: 12-June 08

Re: ADO Connection. Update database INFO in all Computers connected

Posted 17 October 2019 - 09:09 AM

I am always a fan of MSSQL, MYSQL, or Maria. An actual sql server best handles multiple concurrent users.
Was This Post Helpful? 0
  • +
  • -

#5 Luigiview   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-October 19

Re: ADO Connection. Update database INFO in all Computers connected

Posted 17 October 2019 - 11:51 AM

OK, I'll have that in mind. Thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1