CODEWITHSUNDEEP
vb.netms-access
Bruce
Bruce

Reputation: 577

Event in VB.Net when Access updated

I am programming an application using VB.Net. My project is when I click to cell on Excel file and run macro, it will send data from cell to Access. My app just shows the database.

But my problem is how can I trigger in my VB.Net app (refresh app) when a record in Access is changed (records in Access either updated or inserted), or is there any event which will do something when Access is changed?

Upvotes: 2

Views: 969

Answers (1)

Jonathan Applebaum
Jonathan Applebaum

Reputation: 5986

I was curious about FileSystemWatcher() that was mentioned in a comment by @Jimi.
so i wrote a basic program that updates DataGridView after every change in access db file. i have tested it and its working.

clarifications:
1. the purpose of that code is for demonstrating the basic principals regarding to OP question and needs some improvements and modularity.
2. SqlDependency is a much better solution if you can use sql server.

Imports System.IO
Imports System.Data.OleDb
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        UpdateDgv()
        watch()
    End Sub


    Private Sub UpdateDgv()
        DataGridView1.DataSource = Nothing
        DataGridView1.DataSource = GetDatatableFromAccess()
    End Sub

    Private Sub UpdateDgvThread()
        RemoveHandler watcher.Changed, AddressOf OnChanged
        DataGridView1.Invoke(New Action(AddressOf UpdateDgv))
        AddHandler watcher.Changed, AddressOf OnChanged
    End Sub

    Public watcher As FileSystemWatcher

    Private Sub watch()
        watcher = New FileSystemWatcher()
        watcher.Path = "c:\Users\john\Desktop\Desktop 01-04-2017\"
        watcher.NotifyFilter = NotifyFilters.LastWrite
        watcher.Filter = "*.*"
        AddHandler watcher.Changed, AddressOf OnChanged
        watcher.EnableRaisingEvents = True
    End Sub


    Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
        Debug.WriteLine("file was changed")
        ' update datagridview
        Dim t1 As New System.Threading.Thread(AddressOf UpdateDgvThread)
        t1.Start()
    End Sub

    Public Function GetDatatableFromAccess() As DataTable

        Dim connString As String =
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\john\Desktop\Desktop 01-04-2017\Database11.accdb"

        Dim results As New DataTable()

        Using conn As New OleDbConnection(connString)
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TestTable", conn)
            conn.Open()
            Dim adapter As New OleDbDataAdapter(cmd)
            adapter.Fill(results)
        End Using

        Return results
    End Function

End Class

Upvotes: 4

Related Questions