Help connecting a .accdb to a DataGridView object programmatically

[Lucky Hyena]

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

Continue reading...