none
VB.Net count record each time one is created RRS feed

  • Question

  • so i have two users that are admin in my application the can see all the record in the access database but when a new record is added, by a staff member, they have to manually refresh the data grid view by clicking refresh,

    is there a way to do this automatically? so each time the database has a new record it refreshed the Data grid.

    Monday, March 12, 2018 11:28 AM

Answers

  • No you cannot. But you can of course use a timer so the Grid is refreshed by instance every 10 minutes in the method that handles the tick of that.  


    Success
    Cor

    Tuesday, March 13, 2018 12:55 AM

All replies

  • If you are working with data (and I have to ask because there are many ways to work with data from TableAdapter to using a connection and command via a manage data provider) using a TableAdapter which I highly recommend not using then the issues is at the database level outside of Visual Studio.

    Is there a automatic method? Again if using a TableAdapter, no and that is the same for other methods of accessing data but dependent (and you have not specified this) on if you are adding one record or more at a time there are ways to do this but require a fair amount of code. If this was SQL-Server there is a class for doing this.

    If admin have no issues but staff do then this is outside of Visual Studio, instead something with rights (if rights have been setup) inside of ms-access. If that is the case and you can't determine how to fix this myself or another moderator can move your question to a forum for working with ms-access.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 12, 2018 12:02 PM
    Moderator
  • What kind of database are you using?  If your DB supports triggers you could leverage that functionality.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, March 12, 2018 12:04 PM
    Moderator
  • What kind of database are you using?  If your DB supports triggers you could leverage that functionality.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Reed, they indicated ms-access


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 12, 2018 12:14 PM
    Moderator
  • i am not using table adapters but data adapters if they are the same then i do not know, bellow is the class i call on every form to allow for data entry and manipulation
    Imports System.Data.OleDb
    Public Class DBControl
        ' CREATE YOUR DB CONNECTION
        Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                             "Data Source=\\allsaints\DFS$\Tools$\Technician Database\Database.accdb;")
    
        ' PREPARE DB COMMAND
        Public DBCmd As OleDbCommand
    
        ' DB DATA
        Public DBDA As OleDbDataAdapter
            Public DBDT As DataTable
    
            ' QUERY PARAMETERS
            Public Params As New List(Of OleDbParameter)
    
            ' QUERY STATISTICS
            Public RecordCount As Integer
            Public Exception As String
    
            Public Sub ExecQuery(Query As String)
                ' RESET QUERY STATS
                RecordCount = 0
                Exception = ""
    
                Try
                    ' OPEN A CONNECTION
                    DBCon.Open()
    
                    ' CREATE DB COMMAND
                    DBCmd = New OleDbCommand(Query, DBCon)
    
                    ' LOAD PARAMS INTO DB COMMAND
                    Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                    ' CLEAR PARAMS LIST
                    Params.Clear()
    
                    ' EXECUTE COMMAND & FILL DATATABLE
                    DBDT = New DataTable
                    DBDA = New OleDbDataAdapter(DBCmd)
                    RecordCount = DBDA.Fill(DBDT)
                Catch ex As Exception
                    Exception = ex.Message
                End Try
    
                ' CLOSE YOUR CONNECTION
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Sub
    
        Public Function ExecQueryint(Query As String) As Integer
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""
    
            Try
                ' OPEN A CONNECTION
                DBCon.Open()
    
                ' CREATE DB COMMAND
                DBCmd = New OleDbCommand(Query, DBCon)
    
                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                ' CLEAR PARAMS LIST
                Params.Clear()
    
                ' EXECUTE COMMAND & FILL DATATABLE
                DBDT = New DataTable
                'DBDA = New OleDbDataAdapter(DBCmd)
                RecordCount = Convert.ToInt32(DBCmd.ExecuteScalar())
            Catch ex As Exception
                Exception = ex.Message
            End Try
    
            Return RecordCount
            ' CLOSE YOUR CONNECTION
            If DBCon.State = ConnectionState.Open Then DBCon.Close()
        End Function
    
        ' INCLUDE QUERY & COMMAND PARAMETERS
        Public Sub AddParam(Name As String, Value As Object)
                Dim NewParam As New OleDbParameter(Name, Value)
                Params.Add(NewParam)
            End Sub
    End Class
    as you can tell i am new to all this and i am self taught so some thing slip bay me pretty easy

    Monday, March 12, 2018 1:21 PM
  • Hello,

    DataAdapters have the same limitations, there is nothing that allows the admins to see newly added records by staff built in to either Visual Studio, VB.NET or MS-Access.

    A cheesy method would be to add a modified date/time column and a status column e.g. A means added, U for update.

    Place a timer on the form, in the event for when the timer is triggered, query the table for newly added records using the two new columns. You would also need a variables of date time to use for creating a select/where statement to learn if there were new records added since the last check and after the current check reset this variable.

    If this was SQL-Server there is a class for this, there is not a class for ms-access.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 12, 2018 1:55 PM
    Moderator
  • i was thinking that my self but how would the application know that the record status had been changed, if the user did not change the record

    so how would my application know that a record was added, if a different staff member created it

    Monday, March 12, 2018 2:31 PM
  • Reed, they indicated ms-access


    Thanks Karen, I missed that.

    @StevenPaulDavidson

    In theory it should be possible to create a data macro in Access and have it send a UDP broadcast datagram out to the network when a record is added.  Your program can then have a UDP listener setup to receive that broadcast message and update its displayed data.  Some other form of remote communication could be used but a UDP broadcast seems like a decent choice (assuming network configuration allows for it - eg all clients are on the same network segment or all relevant segments have broadcast forwarding enabled).


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, March 12, 2018 3:11 PM
    Moderator
  • No you cannot. But you can of course use a timer so the Grid is refreshed by instance every 10 minutes in the method that handles the tick of that.  


    Success
    Cor

    Tuesday, March 13, 2018 12:55 AM
  • In theory it should be possible to create a data macro in Access and have it send a UDP broadcast datagram out to the network when a record is added. 

    Just an update, the above will only work when Access is used to modify the table.  It does work as I've successfully sent UDP datagrams from Access, but it won't work when using the database file from your VB program.  Apparently the data macros only execute from the Access IDE.

    -EDIT-

    So you are probably stuck with the polling solution as Karen and Cor have suggested.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Tuesday, March 13, 2018 3:59 PM
    Moderator