none
Help connecting a .accdb to a DataGridView object programmatically RRS feed

  • Question

  • I have managed to create a program that programmatically creates a new .accdb file containing a database that is normalised to Third Normal Form, using the ADOX Catalog. My current aim is to be able to load the file into a DataGridView Object and display it, purely through code. My reason for doing this through code rather than setting the file directly as a Data Connection from the form design, is that this is a school project, and I must create the file from scratch and programmatically. Here is the related code:

    Dim createdFileLocation As String = fileDirectory & databaseFileName 
    fileCreator.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & createdFileLocation) 'establishes a connection to a database file by creating it
    databaseCreationProcedure(fileCreator) 'run the database creation and normalisation procedure on the created file
    DataGridView1.DataSource = createdFileLocation

    I would have thought that simply setting the full directory of the file as the value for DataGridView1.DataSource I would be able to display it, but I just get the default gray screen on the Data Grid object in the form when I run the program.

    In case it is relevant, here is the code for the database layout itself:

            'Create fully normalised database:
            'Patient Information Table
            Dim patientsTable As New ADOX.Table
            patientsTable.Name = "Patient"
            With patientsTable.Columns
                .Append("Patient ID")
                .Append("Name")
                .Append("Address")
                .Append("Post Code")
                .Append("Date Of Birth")
                .Append("Last Appointment")
            End With
            fileCreator.Tables.Append(patientsTable)
            patientsTable = Nothing
    
            'Appointment table
            Dim appointmentsTable As New ADOX.Table
            appointmentsTable.Name = "Appointments"
            With appointmentsTable.Columns
                .Append("Appointment ID")
                .Append("Appointment Date")
                .Append("Appointment Time")
                .Append("Treatment Cost")
                .Append("Patient ID")
            End With
            fileCreator.Tables.Append(appointmentsTable)
            appointmentsTable = Nothing
    
            'X-Ray Table
            Dim xrayTable As New ADOX.Table
            xrayTable.Name = "X-Rays"
            With xrayTable.Columns
                .Append("X-Ray ID")
                .Append("Patient ID")
                .Append("X-Ray Image")
            End With
            fileCreator.Tables.Append(xrayTable)
            xrayTable = Nothing

    I am still not sure how to set columns or fields as Primary Keys for each table, however that is a problem for another time. To be thorough, the primary keys are Patient ID, Appointment ID, and X-Ray ID. Thanks very much :)


    • Edited by Lucky Hyena Thursday, January 16, 2020 3:46 PM Edited title
    Thursday, January 16, 2020 3:45 PM

All replies

  • Here is an example for loading a DataTable here is a very simple example, in this case combine two related table into a DataTable where the DataTable would be set as the DataSource of a DataGridView.

    Original code is here.

    Public Function LoadCustomers() As DataTable
    
        Using cn As New OleDbConnection("Your connection string")
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = <SQL>
                    SELECT 
                        C.Identifier, 
                        C.CompanyName, 
                        CT.ContactTitleId, 
                        CT.Title, C.Address, 
                        C.City, C.PostalCode, 
                        C.Country
                    FROM 
                        ContactTitle AS CT 
                    INNER JOIN 
                        Customers AS C ON CT.ContactTitleId = C.ContactTitleId
                    ORDER BY 
                        CompanyName;
                    </SQL>.Value
    
                Dim dt As New DataTable With {.TableName = "Customer"}
    
                Try
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                    '
                    ' Hide primary keys
                    '
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    dt.Columns("ContactTitleId").ColumnMapping = MappingType.Hidden
    
                Catch ex As Exception
    				' decide how to handle exception
                End Try
    
                Return dt
    
            End Using
        End Using
    End Function
    

    If no DataGridView columns are set the DataGridView uses DataColumns from the DataTable, if you have pre defined DataGridView columns set each column DataPropertyName to a DataColumn name and on the DataGridView set AutoGenerateColumns to False


    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

    Thursday, January 16, 2020 3:57 PM
    Moderator