none
Library Project using Visual basic 2015 and Access database.

Answers

  • Hi sheelnath,
    I put one DataGridView control in Form, and I create Table in Access Database, then Imports System.Data.OleDb  and  do one sample you can refer to.

    Private Sub Form7_Load(sender As Object, e As EventArgs)
            'Bindgrid();
            Loaddata()
        End Sub
    
        Private Sub Loaddata()
            Dim dt As New DataTable()
            Dim strconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database1.accdb; Persist Security Info=False;"
            Dim sql = "select * from table1;"
            Using con As New OleDbConnection(strconn)
                con.Open()
                Using com As New OleDbCommand(sql, con)
                    Dim adapter As New OleDbDataAdapter(com)
                    adapter.Fill(dt)
    
                    dataGridView1.DataSource = dt
                End Using
    
                con.Close()
            End Using
    End Sub
    

    More info about connecting Access database, please refer to How to Connect Visual Basic.Net to MS Access Database.

    Best Regards,

    Cherry Bu


    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 sheelnath Saturday, January 27, 2018 3:28 PM
    Friday, March 24, 2017 2:47 AM
    Moderator
  • Hello,

    We can go in several directions here and several ways to implement e.g. with wizards mentioned below classes are generated for you and for the most part are on a per-form basis meaning the classes generated work for one form but not another form without extra work while a DataAdapter or connection/command method can be written directly in a form or better yet in a class that can be used in any form.

    Many who first start out with data access be in MS-Access or SQL-Server is to use data wizards (see the following page for examples and skip the Silverlight ones).

    There are variations of working with a managed data provider, using DataAdapter or work with a connection and command object.

    Here is an example class for reading, inserting, updating and deleting data. Note that this is one view of this way of working with data.

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        ''' <summary>
        ''' Used by the caller, a BindingSource component provides many great features
        ''' so I recommend you read up on it.
        ''' </summary>
        ''' <returns></returns>
        Public Property CustomerBindingSource As BindingSource
        Public Sub New()
            CustomersDataTable = New DataTable
            'GetCustomerData()
            CustomerBindingSource = New BindingSource
            CustomerBindingSource.DataSource = CustomersDataTable
        End Sub
        Public Sub GetCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                                SELECT Identifier, ContactTitle, Country, CompanyName
                                FROM Customers 
                                ORDER BY CompanyName ASC
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
                    Console.WriteLine()
                End Using
            End Using
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
    
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal Name As String, ByVal Contact As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
                        cmd.Parameters.AddWithValue("@Identifier", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal Name As String, ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (ContactName) Values(@ContactName)"
    
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class
    
    Depending on the user interface needed you might simply load data into a DataGridView and perhaps some controls such as TextBox controls where you might bind controls to data using a BindingSource e.g. firstNameTextBox.DataBinding.Add(...

    The following code sample shows working with connection/command to add new records which if not done before can be a bit challenging to get the new primary key but with this sample it's easy.

    I mentioned DataAdapter before, here is a fairly decent example for using them to do a master-detail app. The code is easy to follow but not simple. It's spread out between several projects eg.

    So in a nutshell one example may not be the right direction so you need to decide the proper path, data wizards, data adapters or connection/commands.

    One last thing, designing the database schema is important no matter which path is taken especially with related data e.g. customer, orders, order details, products etc.

    Let's look at a decent database design. Many might store contact information in the same table as Customers below but by breaking it out changes are easier by using a foreign key. Here I'm storing contact title in it's own table and used in Customers. We can get more efficient too, this is a simple example. At work for contacts we have five or so tables to break out contacts.


    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, March 24, 2017 10:23 AM
    Moderator

All replies

    • Proposed as answer by Frank L. Smith Thursday, March 23, 2017 5:11 PM
    Thursday, March 23, 2017 3:48 PM
  • Hi sheelnath,
    I put one DataGridView control in Form, and I create Table in Access Database, then Imports System.Data.OleDb  and  do one sample you can refer to.

    Private Sub Form7_Load(sender As Object, e As EventArgs)
            'Bindgrid();
            Loaddata()
        End Sub
    
        Private Sub Loaddata()
            Dim dt As New DataTable()
            Dim strconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database1.accdb; Persist Security Info=False;"
            Dim sql = "select * from table1;"
            Using con As New OleDbConnection(strconn)
                con.Open()
                Using com As New OleDbCommand(sql, con)
                    Dim adapter As New OleDbDataAdapter(com)
                    adapter.Fill(dt)
    
                    dataGridView1.DataSource = dt
                End Using
    
                con.Close()
            End Using
    End Sub
    

    More info about connecting Access database, please refer to How to Connect Visual Basic.Net to MS Access Database.

    Best Regards,

    Cherry Bu


    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 sheelnath Saturday, January 27, 2018 3:28 PM
    Friday, March 24, 2017 2:47 AM
    Moderator
  • Hello,

    We can go in several directions here and several ways to implement e.g. with wizards mentioned below classes are generated for you and for the most part are on a per-form basis meaning the classes generated work for one form but not another form without extra work while a DataAdapter or connection/command method can be written directly in a form or better yet in a class that can be used in any form.

    Many who first start out with data access be in MS-Access or SQL-Server is to use data wizards (see the following page for examples and skip the Silverlight ones).

    There are variations of working with a managed data provider, using DataAdapter or work with a connection and command object.

    Here is an example class for reading, inserting, updating and deleting data. Note that this is one view of this way of working with data.

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        ''' <summary>
        ''' Used by the caller, a BindingSource component provides many great features
        ''' so I recommend you read up on it.
        ''' </summary>
        ''' <returns></returns>
        Public Property CustomerBindingSource As BindingSource
        Public Sub New()
            CustomersDataTable = New DataTable
            'GetCustomerData()
            CustomerBindingSource = New BindingSource
            CustomerBindingSource.DataSource = CustomersDataTable
        End Sub
        Public Sub GetCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                                SELECT Identifier, ContactTitle, Country, CompanyName
                                FROM Customers 
                                ORDER BY CompanyName ASC
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
                    Console.WriteLine()
                End Using
            End Using
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
    
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal Name As String, ByVal Contact As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
                        cmd.Parameters.AddWithValue("@Identifier", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal Name As String, ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (ContactName) Values(@ContactName)"
    
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class
    
    Depending on the user interface needed you might simply load data into a DataGridView and perhaps some controls such as TextBox controls where you might bind controls to data using a BindingSource e.g. firstNameTextBox.DataBinding.Add(...

    The following code sample shows working with connection/command to add new records which if not done before can be a bit challenging to get the new primary key but with this sample it's easy.

    I mentioned DataAdapter before, here is a fairly decent example for using them to do a master-detail app. The code is easy to follow but not simple. It's spread out between several projects eg.

    So in a nutshell one example may not be the right direction so you need to decide the proper path, data wizards, data adapters or connection/commands.

    One last thing, designing the database schema is important no matter which path is taken especially with related data e.g. customer, orders, order details, products etc.

    Let's look at a decent database design. Many might store contact information in the same table as Customers below but by breaking it out changes are easier by using a foreign key. Here I'm storing contact title in it's own table and used in Customers. We can get more efficient too, this is a simple example. At work for contacts we have five or so tables to break out contacts.


    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, March 24, 2017 10:23 AM
    Moderator