multiple users

[Retelist]

I have a Access database installed on a server. I have made an vb application (App1) that interogate Access and show data on a monitor, from a diferent PC I input new data (App2). A need that the App1 re-query the database periodically so the App1 monitor to show real information (including new data ).

App1:

Start:
             Con.Open()
              Dim ds As New DataSet
              Dim dt As New DataTable

                ds.Tables.Add(dt)
                Dim da As New OleDbDataAdapter
                da = New OleDbDataAdapter("SELECT [...);", Con)
                da.Fill(dt)
                DataGridView1.DataSource = dt.DefaultView
          
                Con.Close()
                Thread.Sleep(500)
Goto Start

My code is not working...

 

[jmcilhinney]

Get rid of that GoTo and never use GoTo ever again. If you have a section of code that you want to repeat over and over then you put it in a loop, i.e. For, For Each, Do or While, depending on the circumstances. In this case though, you would not use a loop and you would not Sleep the main thread. You can use a Timer with an Interval of 500 if you want to do something every half a second. You can then handle its Tick event and put your code in the event handler.

You can also get rid of your DataSet as it's pointless and just bind the DataTable and not its DefaultView because the data comes from the DefaultView when binding a Datatable anyway. That said, should you really be creating a new DataTable every time? Would it not make more sense to just create one and repopulate that?

Finally, do you really need data to be no more than half a second old? If so then an Access database is the wrong choice to begin with.

 

[Retelist]

I guess that 2-3 seconds is an acceptable time to reinterogate or to reinterogate every time when the database is changed...

 

[jmcilhinney]

How much data are we talking about? Will the user be working with this data?

 

[Retelist]

App2: 20-30 inputs per day (orders, invoices), App1 only show the data on a big monitor (no inputs)

 

[IanRyder]

Hi,

Since you are only talking about 20 to 30 inputs per day I would suggest a slightly different approach.

Using your current logic you re-populate your entire DataTable every time the Timer fires (assuming you have taken on board jcmilhinney's comments) which is, lets say, set to every 2 seconds. However, this is wasted Processing Power and nore importantly unnecessary Traffic across your internal Network.

I would suggest then that you do something like this:-

1) The FIRST time you populate your DataTable record the Count of the number of Rows in the DataTable.

2) Then in the Timer use a Command object which takes a simple SQL query on the lines of:-

Select Count(*) From YourTable .....

You can then use the ExecuteScalar method of the Command object to return the Total Number of records in the Access DataTable.

3) If the Total Number of records returned in point 2 is Greater than the number of Rows saved in point 1, then, and only then, do you re-populate your DataTable and then re-save the new number of rows in the DataTable.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

Hi,

Since you are only talking about 20 to 30 inputs per day I would suggest a slightly different approach.

Using your current logic you re-populate your entire DataTable every time the Timer fires (assuming you have taken on board jcmilhinney's comments) which is, lets say, set to every 2 seconds. However, this is wasted Processing Power and nore importantly unnecessary Traffic across your internal Network.

I would suggest then that you do something like this:-

1) The FIRST time you populate your DataTable record the Count of the number of Rows in the DataTable.

2) Then in the Timer use a Command object which takes a simple SQL query on the lines of:-

Select Count(*) From YourTable .....

You can then use the ExecuteScalar method of the Command object to return the Total Number of records in the Access DataTable.

3) If the Total Number of records returned in point 2 is Greater than the number of Rows saved in point 1, then, and only then, do you re-populate your DataTable and then re-save the new number of rows in the DataTable.

Hope that helps.

Cheers,

Ian

If the existing records are being edited and/or deleted then that approach fails.

If existing records are not being edited then you don't even need the count, nor do you need to rebind at all. You simply use a sequential ID and then query the database for records where the ID is greater than the greatest value already in your DataTable. You use a data adapter to Fill the existing DataTable and the new records are added. If there are no new records then there's no data sent at all; not even a count.

If data is being edited and or deleted then you should add a time stamp to each record each time it's edited. You can then query for records with a time stamp later then the latest value in your DataTable. Again, you Fill the existing DataTable each time and no modified records means no traffic at all.

Just note that this means that records cannot be physically deleted, but rather flagged as inactive. You'd then filter the data displayed to hide inactive records.

 

[Retelist]

The most difficult part is to understand what jimcihinney explain.

Until now I get rid of the Goto and I created a Tminer event. But I need more help to get rid of my DataSet and just bind the DataTable.

Imports System.Data.OleDb


Public Class frmStoc

    Dim Con As New OleDbConnection

    Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...Database1.accdb"

        Timer1.Enabled = True
        Timer1.Interval = 5000
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        Con.Open()
       [COLOR=#ff0000] Dim ds As New DataSet[/COLOR]
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter
        da = New OleDbDataAdapter("SELECT [....);", Con)
        da.Fill(dt)
      [COLOR=#ff0000]  DataGridView1.DataSource = dt.DefaultView[/COLOR]
        Con.Close()

    End Sub
End Class

Is there an example/tutorial?

 

[jmcilhinney]

I brought up some fairly important points in my last post that you haven't bothered to address. Are records being updated and/or deleted or are you just adding new records?

As for the DataSet, just get rid of it. You're not even using it anyway.

 

[Retelist]

I just add new records. But, if a record contain an error, it will be edited.

 

[jmcilhinney]

I just add new records. But, if a record contain an error, it will be edited.

If you might be editing records then you're not just adding records, so why say that you're just adding records? It's like you're trying to confuse the issue. Anyway, I suggested an approach that you should use in post #7.

 

[Retelist]

"It's like you're trying to confuse the issue"

No. I am confuzed, VB is still a big mistery for me.
I may add or edit data to the database, but not using this form; for add or edit I will use others forms.

The code that work perfectly for me:

Private Sub frmStoc_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...Database1.accdb"

        Timer1.Enabled = True
        Timer1.Interval = 5000
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        Con.Open()
       
        Dim dt As New DataTable
     
        Dim da As New OleDbDataAdapter
        da = New OleDbDataAdapter("SELECT [...);", Con)
        da.Fill(dt)
        
        DataGridView1.DataSource = dt
       
        Con.Close()


    End Sub

 

[jmcilhinney]

It may get the job done, but it is far from perfect.