none
Integrating Access Database URGENT HELP NEEDED*** RRS feed

  • Question

  • Hi Guys,

    I'm somewhat new to Visual Basic coding so please forgive me if i ask some stupid questions. I currently own a dog boarding company and want to be able to use a bespoke program to allow me to store all my client information. I believe this is possible using visual basic to input, edit and view data from a Microsoft Access Database Table. I have currently gone quite far into developing the program and have a main menu when opening it as shown in the following screenshot. 

    When i click on the 'Search Records' function, it opens a new form which is shown below. Currently nothing on this form is functional except the 'exit' button. What i am needing much help with is to be able to use my database from access to populate the text boxes with data. I have already followed a tutorial on creating a Dataset with my database so this has been done. Also, i want to be able to use the search text box at the top to be able to enter a search query (this can be text from any of the available text boxes) and display records which match this. Then it would just be a case of pressing 'Prev' and 'Next' buttons to navigate through the search results with each result showing its data in the other fields. I hope this has made sense and i haven't confused too much. If you need any clarification just shoot me with any questions!

    Also, if you're feeling extra generous, let me know how i can code the 'delete' button to delete records and the 'refresh' button to clear all search results and data in the text fields. Don't worry about the button labelled 'New' as i have a different form which i want to use to input data into the database.

    Any help is much appreciated!

    Friday, July 6, 2018 10:13 PM

All replies

  • Hello,

    The following will outline ideas from what has been presented in the search records form for providing a functional search/filter operation were the operator can type in field data for any input (at this point TextBox controls).

    If you simply use TextBox controls and say type in “Dog name” and misspell the dog’s name nothing will come up with unless you provide “starts with”, “contains”, “ends with” or “equals”. A better method would be to setup “Dog name” with auto-complete functionality, same for the others so when the operator starts typing auto-complete assist and there are no misspelled words. The same goes for other controls also.

    How to put together a filter from the above, you would need to check each control to see if there is a value, if so that value becomes part of the filter. Decisions such as using AND and/or OR in the filter need to be decided and it’s best to stick with AND conditions, OR conditions can get complicated if you are new to programming.

    How to code this? Well first it’s dependent on how you loaded the DataSet as there are a handful of ways to do this so more details (the more details the better) from you on how data operations are currently setup. Once known myself or others may move you forward or suggest a different path to follow. Personally any code samples I provide will not be at a novice level as shown in this MSDN code sample.

    Also note that dependent on your database schema suggestions may be made e.g. you should have a table for "dog breed", "customer", a "customer contact" table and more...

    In regards to auto-complete, refer to my MSDN code sample performing auto-complete on both TextBox and ComboBox controls. The database used is 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

    Saturday, July 7, 2018 12:47 AM
    Moderator
  • Hi Karen,

    Thanks for your reply! I have now managed to integrate the database successfully and can display records and navigate through them. One thing i am having difficulty with it updating the records and creating new ones. 

    I have created the following for the 'update' form & button

    Public Class SearchRecords
        Dim maxrows As Integer
        Dim incdec As Integer
        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        Dim dbsource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Private Sub SearchRecords_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Customer_Information_PPNSDataSet.Customer_Information' table. You can move, or remove it, as needed.

            dbprovider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
            dbsource = "Data Source = \\10.1.10.2\User Profiles\Customer Information PPNS.accdb"
            con.ConnectionString = dbprovider & dbsource
            con.Open()
            sql = "SELECT * From Customer_Information"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Customer_Information")
            TextBox2.Text = ds.Tables("Customer_Information").Rows(0).Item(1)
            TextBox3.Text = ds.Tables("Customer_Information").Rows(0).Item(2)
            TextBox4.Text = ds.Tables("Customer_Information").Rows(0).Item(3)
            TextBox6.Text = ds.Tables("Customer_Information").Rows(0).Item(5)
            TextBox7.Text = ds.Tables("Customer_Information").Rows(0).Item(6)
            TextBox8.Text = ds.Tables("Customer_Information").Rows(0).Item(7)
            TextBox9.Text = ds.Tables("Customer_Information").Rows(0).Item(8)
            TextBox10.Text = ds.Tables("Customer_Information").Rows(0).Item(4)
            TextBox11.Text = ds.Tables("Customer_Information").Rows(0).Item(9).Value.ToString
            TextBox12.Text = ds.Tables("Customer_Information").Rows(0).Item(10)
            RichTextBox1.Text = ds.Tables("Customer_Information").Rows(0).Item(12)
            CheckBox1.Checked = ds.Tables("Customer_Information").Rows(0).Item(11)

            maxrows = ds.Tables("Customer_Information").Rows.Count
            incdec = -1

        End Sub
        Private Sub Navigate()
            TextBox2.Text = ds.Tables("Customer_Information").Rows(incdec).Item(1)
            TextBox3.Text = ds.Tables("Customer_Information").Rows(incdec).Item(2)
            TextBox4.Text = ds.Tables("Customer_Information").Rows(incdec).Item(3)
            TextBox6.Text = ds.Tables("Customer_Information").Rows(incdec).Item(5)
            TextBox7.Text = ds.Tables("Customer_Information").Rows(incdec).Item(6)
            TextBox8.Text = ds.Tables("Customer_Information").Rows(incdec).Item(7)
            TextBox9.Text = ds.Tables("Customer_Information").Rows(incdec).Item(8)
            TextBox10.Text = ds.Tables("Customer_Information").Rows(incdec).Item(4)
            TextBox11.Text = ds.Tables("Customer_Information").Rows(incdec).Item(9).Value.ToString
            TextBox12.Text = ds.Tables("Customer_Information").Rows(incdec).Item(10)
            RichTextBox1.Text = ds.Tables("Customer_Information").Rows(incdec).Item(12)
            CheckBox1.Checked = ds.Tables("Customer_Information").Rows(incdec).Item(11)
        End Sub

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If incdec = -1 Then
                incdec = 0
            End If
            Dim cb As New OleDb.OleDbCommandBuilder(da)

            ds.Tables("Customer_Information").Rows(incdec).Item(1) = TextBox2.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(2) = TextBox3.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(3) = TextBox4.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(5) = TextBox6.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(6) = TextBox7.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(7) = TextBox8.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(8) = TextBox9.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(4) = TextBox10.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(9) = TextBox11.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(10) = TextBox12.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(12) = RichTextBox1.Text
            ds.Tables("Customer_Information").Rows(incdec).Item(11) = CheckBox1.Checked

            da.Update(ds, "Customer_Information")

            MsgBox("Record Succesfully Updated", MsgBoxStyle.OkOnly)

        End Sub

        

    I hope you don't mind having a look at this. Everything is working except i am getting a SYNTAX error on the da.Update(ds "Customer_Information") command. I followed a guide online and it seemed to work great for everybody else but i don't know how this syntax should be and what i am doing wrong.

    Any updates are appreciated!

    Saturday, July 14, 2018 1:36 AM
  • Also, Please find attached a link to the guide i have been following;

    https://www.youtube.com/watch?v=uPBsL4FxEYE&index=28&list=PL30B6E998C1DBC793

    Saturday, July 14, 2018 1:40 AM
  • I would offer a different approach (which may be considered complex but gives you absolute control over all oerations), first all data operations should be in a data class with code that handles exceptions built in. For displaying data from the table into TextBox controls use data binding (and give the TextBox controls meaningful names) e.g. txtFirstName.DataBinding.Add(“Text”,dt,”FirstName”) where ‘dt’ is the DataTable. Note that DataSet containers are only need when dealing with relational data at the current time.

    Use a BindingSource component, many advantages to use them including navigation and optionally use a BindingNavigator control.

    Going with the above concepts see my MSDN code sample which shows a path for read, edit, add and remove.

    An idea for searching can be done in the BindingNavigator (the follow image is from a code sample I did for C# but could be done in VB.NET)

    In closing I have not touched on validation in any of the above which should be considered also in regards to verifying that when add, editing data the data to place into the database table is valid.

    Going back to your syntax error, there are many so you need to indicate what it is if you don't want to follow the above ideas.


    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

    Saturday, July 14, 2018 11:22 AM
    Moderator
  • Hi Karen,

    Thanks for your quick response. After having read over your guides i do think this is somewhat out of my remit. Although, i definitely like the idea of the search function etc. How would you feel about remoting onto my PC using Teamviewer and showing me some of this configuration? I am much more of a visual learner although if you do not feel comfortable doing this I completely understand. 

    Thanks,
    Callum

    Saturday, July 14, 2018 12:17 PM
  • Hi Karen,

    Thanks for your quick response. After having read over your guides i do think this is somewhat out of my remit. Although, i definitely like the idea of the search function etc. How would you feel about remoting onto my PC using Teamviewer and showing me some of this configuration? I am much more of a visual learner although if you do not feel comfortable doing this I completely understand. 

    Thanks,
    Callum

    Just an FYI... at the point you are wanting live one-on-one training, you're moving into paid services.  Consider CodeMentor.io for that purpose.

    Have you looked through the documentation for ADO.Net?  The basic databinding you need should not be too complex to pick up.  Once you have the dataset/table/view concepts, using a BindingSource component to bind a datatable to your form controls shouldn't be too bad.


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

    Saturday, July 14, 2018 1:09 PM
    Moderator