5.7 - Integrating Access and VB (2) - Displaying Data via Code

Using VB to pull data from an access database, this pulls data from both tables and queries, only using code in this instance.

Here's the code:

Imports System.Data.OleDb

Public Class Form1

Dim dbConnection As OleDbConnection
Dim dbCommand As OleDbCommand
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = HospitalDetails.mdb"
Dim dtHospital As DataTable

Private Sub btnWards_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWards.Click

dbConnection = New OleDbConnection(ConnectString) 'Connects to database
dbDataAdapter = New OleDbDataAdapter()
dtHospital = New DataTable()
dbCommand = New OleDbCommand("Wards") 'Command Object is the Wards Table
dbCommand.CommandType = CommandType.TableDirect 'ditto above
dbDataAdapter.SelectCommand = dbCommand 'Sets the data adapter to that table (Wards)
dbDataAdapter.SelectCommand.Connection = dbConnection
dbConnection.Open() 'Opens the Database Connection
dbDataAdapter.Fill(dtHospital) 'Pull data into the data table from the data adapter (from the table)
grdHospital.DataSource = dtHospital 'Fill the data grid using the data table
dbConnection.Close() 'Close the connection to the database

End Sub


Private Sub btnPatients_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPatients.Click

Dim dtHospital As New DataTable()
dbDataAdapter = New OleDbDataAdapter("SELECT * FROM Patients", ConnectString)
dbDataAdapter.Fill(dtHospital)
grdHospital.DataSource = dtHospital

End Sub

Private Sub btnPatientsAndWards_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPatientsAndWards.Click

Dim dsPatients As New DataSet()
dbConnection = New OleDbConnection(ConnectString)
dbDataAdapter = New OleDbDataAdapter()
dbCommand = New OleDbCommand()
dbCommand.CommandText = "qryPatients"
dbCommand.CommandType = CommandType.StoredProcedure
dbDataAdapter.SelectCommand = dbCommand
dbDataAdapter.SelectCommand.Connection = dbConnection
dbConnection.Open()
dbDataAdapter.Fill(dsPatients, "qryPatients")
grdHospital.DataSource = dsPatients
grdHospital.DataMember = "qryPatients"
dbConnection.Close()

End Sub

Category:

Education

Tags:

• Access
• Visual
• Basic
• Integration
• ACCDB
• MDB
• OLE
• ADO
• .Net
• Tables
• Queries
• VB

[fm10032003]

thanks but i wish to know where is the "grdHospital".Datasource=dtcat­alog from?

[Firchild]

@fm10032003 That just fills the datagrid (table) with data dthospital. I don't know how you've got dtcatalog there.. That's nowhere in my code! Sorry it took me so long to post a response.

[BlueCoyote007]

Good video and I would say better subjects than most access videos..

[habibsozib]

thanks a lot for your code, its really help me to do my project

[DLiberator78]

Thanks for this tutorial, really helpful tutorial. Now I have a better understanding of how to integrate my database within Visual Basic and also some more VB coding. Which can be quite daunting at first. Great tutorial as always.