locked
Access and datagridview RRS feed

  • Question

  • Hi,

    I know its possible to make changes to a database on access from a datagridview but i want the opposite.

    Is it possible to go on to access make a change to my tables and then save and have the same changes made automatically on my datagridview?

    Any advise is welcome or any other ways of doing this?

    i need to make it very easy for changes to be made to my data table but i cant find anyway to make these changes happen without redoing the table over each time it changes.

    Thanks

    lolbird

    Wednesday, August 19, 2020 6:43 AM

Answers

  • It's not possible with Microsoft Access, if this was SQL-Server it's possible via SqlDependency class and even with that there are a) restrictions b) setup in SSMS for this to work.

    Super simple example for SQL Server.

    Sub Initialization()
        ' Create a dependency connection.
        SqlDependency.Start(connectionString, queueName)
    End Sub
    
    Sub SomeMethod()
        ' Assume connection is an open SqlConnection.
        ' Create a new SqlCommand object.
        Using command As New SqlCommand( _
          "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", _
          connection)
    
            ' Create a dependency and associate it with the SqlCommand.
            Dim dependency As New SqlDependency(command)
            ' Maintain the refernce in a class member.
            ' Subscribe to the SqlDependency event.
            AddHandler dependency.OnChange, AddressOf OnDependencyChange
    
            ' Execute the command.
            Using reader = command.ExecuteReader()
                ' Process the DataReader.
            End Using
        End Using
    End Sub
    
    ' Handler method
    Sub OnDependencyChange(ByVal sender As Object, _
        ByVal e As SqlNotificationEventArgs)
        ' Handle the event (for example, invalidate this cache entry).
    End Sub
    
    Sub Termination()
        ' Release the dependency
        SqlDependency.Stop(connectionString, queueName)
    End Sub


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by lolbird1212 Thursday, August 20, 2020 1:39 PM
    Thursday, August 20, 2020 10:36 AM

All replies

  • Hi lolbird1212,

    Thank you for posting here.

    When you make a change to your table in access, you can query again from your access database.

    Here's the code you can refer to.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As DataTable = New DataTable()
    
            Using myConn As OleDbConnection = New OleDbConnection(connString)
                myConn.Open()
                Dim cmdText As String = "SELECT * FROM YourTable "
                Dim cmd As OleDbCommand = New OleDbCommand(cmdText, myConn)
                Dim reader = cmd.ExecuteReader()
                dt.Load(reader)
                DataGridView1.DataSource = dt
            End Using
    
        End Sub

    Hope it could be helpful.

    Besides, if I have any misunderstanding, please provide more details.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by lolbird1212 Thursday, August 20, 2020 8:35 AM
    • Unmarked as answer by lolbird1212 Thursday, August 20, 2020 10:28 AM
    Thursday, August 20, 2020 2:15 AM
  • Hi 

    Thank you for your time and help.

    I'm trying to use datagridview as just a visual table that will need updating often and my thinking was to make it easy as possible if i could make a change to my tables in access and have it update the datagridview automatically (no work on visual basic).

    Not sure if this is possible or not but any help would be great.

    If you need any further info please message me and i will give you much as i can. I am a complete beginner to code so have little idea of what i'm doing at the moment.

    Lolbird

    Thursday, August 20, 2020 10:27 AM
  • It's not possible with Microsoft Access, if this was SQL-Server it's possible via SqlDependency class and even with that there are a) restrictions b) setup in SSMS for this to work.

    Super simple example for SQL Server.

    Sub Initialization()
        ' Create a dependency connection.
        SqlDependency.Start(connectionString, queueName)
    End Sub
    
    Sub SomeMethod()
        ' Assume connection is an open SqlConnection.
        ' Create a new SqlCommand object.
        Using command As New SqlCommand( _
          "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", _
          connection)
    
            ' Create a dependency and associate it with the SqlCommand.
            Dim dependency As New SqlDependency(command)
            ' Maintain the refernce in a class member.
            ' Subscribe to the SqlDependency event.
            AddHandler dependency.OnChange, AddressOf OnDependencyChange
    
            ' Execute the command.
            Using reader = command.ExecuteReader()
                ' Process the DataReader.
            End Using
        End Using
    End Sub
    
    ' Handler method
    Sub OnDependencyChange(ByVal sender As Object, _
        ByVal e As SqlNotificationEventArgs)
        ' Handle the event (for example, invalidate this cache entry).
    End Sub
    
    Sub Termination()
        ' Release the dependency
        SqlDependency.Stop(connectionString, queueName)
    End Sub


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by lolbird1212 Thursday, August 20, 2020 1:39 PM
    Thursday, August 20, 2020 10:36 AM