none
Database editing issue RRS feed

  • Question

  • Greetings.

    Editing a .accdb file is easy via some buttons and below codes + importing the file as data source in VS 2013

    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
            Table1BindingSource.AddNew()
        End Sub
    
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            Try
    
                Table1BindingSource.EndEdit()
    
                Table1TableAdapter.Update(Database_productsDataSet.Table1)
                MsgBox("saved!")
            Catch ex As Exception
                MsgBox(ex.Message)
    
            End Try
    
    
    
        End Sub
    
        Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
            Table1BindingSource.RemoveCurrent()
        End Sub

    But what If I want to make an application in which user browses one .accdb file and application does the same thing like above  with the same buttons in order to edit and save the .accdb file.  ?

    Thanks in advance


    • Edited by Kevin993 Friday, February 23, 2018 1:00 PM
    Friday, February 23, 2018 12:59 PM

All replies

  • Not exactly sure what you are asking but can tell you that when using TableAdapter method to access database tables you can only work with databases that met the strong type classes that were generated and available via the TableAdapter/DataSet/BindingSource.

    So let's say you created the above for a customer table with Id and CompanyName then try to read another access database with customer table with id and name, that would not fly.


    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

    Friday, February 23, 2018 1:08 PM
    Moderator
  • Not exactly sure what you are asking but can tell you that when using TableAdapter method to access database tables you can only work with databases that met the strong type classes that were generated and available via the TableAdapter/DataSet/BindingSource.

    So let's say you created the above for a customer table with Id and CompanyName then try to read another access database with customer table with id and name, that would not fly.


    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

    You got the question just right,

    So I have no any choice else but using queries and command texts to do so. Right ?

    Friday, February 23, 2018 1:16 PM
  • Correct. That is, unless the database the user is selecting is identical to the one that was used when creating the TableAdapters through the Data Designer.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 23, 2018 1:27 PM
  •  

    You got the question just right,

    So I have no any choice else but using queries and command texts to do so. Right ?

    Yes that is correct, you need to move from typed data sets to (in a simple case) using a connection and command.

    Depending on how deep you need to go, you could let a user browse, select a database, you then get all the tables and for each table gets column names. Allow them to select a table, select columns and then create a SQL SELECT to populate a DataTable. Now there are a great many moving parts to this, not simple as we also need to take into consideration building classes and forms to allow that to happen.

    It would be easier to select the database, get table names, use SELECT * FROM some table.

    It's easy to get table names given a connection string.

    Imports System.Data.OleDb
    Public Class TableHelper
        Public Function Tables(
            pConnectionString As String) As DataTable
    
            Dim dt = New DataTable
    
            Using cn As New OleDbConnection(pConnectionString)
                cn.Open()
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _
                    {Nothing, Nothing, Nothing, "TABLE"})
            End Using
    
            Return dt
    
        End Function
    End Class
    


    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

    Friday, February 23, 2018 1:46 PM
    Moderator
  • Hi Kevin993,

    According to your description, I guess that you have added one project data source for your application, like the following screenshot:

    Now you want to browser one .accdb file, you need to use oledbconnection in your code:

    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataBase\Database1.accdb;Persist Security Info=False;"
            Dim sql As String = "Select Id, Name, Speaker as [language] from Test3"
            Using conn As New OleDbConnection(str)
                conn.Open()
                Using cmd As New OleDbCommand(sql, conn)
                    Dim adapter As New OleDbDataAdapter(cmd)
    
                    adapter.Fill(dt)
    
                End Using
                conn.Close()
            End Using

    You can change str string to open other database.

    Best Regards,

    Cherry


    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.

    Saturday, February 24, 2018 6:05 AM
    Moderator