William Dal William Dal - 1 year ago 67
Vb.net Question

Retrieving All Tables in MS ACCESS

I am having trouble on how to retrieve 2 fields of every tables in my Database. I have 3 Tables saved in my MS Access, namely: RegularPassenger, DisablePassenger, and VIPPassenger, and every tables has 2 fields each, which are SeatsNo and Filled.

The scenario is that I wanted to let the user see if their selected seat/s are already RESERVED which indicates a colored button if its already been reserved. The codes I have done so far is that I can only retrieve one Table.

The codes are:

Imports System
Imports System.Data.OleDb

Public Class SeatsAlignment
Dim con As New System.Data.OleDb.OleDbConnection
Dim cmd As New System.Data.OleDb.OleDbCommand
Dim CntRw As Integer
Private Function CheckReservation(ByVal StNo As String) As Boolean
If con.State = ConnectionState.Open Then con.Close()
con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\laptop\Documents\Trial\Sample.accdb")
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select Count(*) From RegularPassenger Where SeatNo=@StNo And Filled=@StFld"
cmd.Parameters.Add("@StNo", OleDb.OleDbType.VarChar, 10).Value = StNo
cmd.Parameters.Add("@StFld", OleDb.OleDbType.VarChar, 10).Value = "RESERVED"
cmd.Connection = con
CntRw = cmd.ExecuteScalar()
Return IIf(CntRw > 0, True, False)
End Function
Private Sub SeatsAlignment_Load(sender As Object, e As EventArgs) Handles MyBase.Load
RPS.Enabled = False
DPS.Enabled = False
VPS.Enabled = False
TextBox1.Enabled = False
TextBox2.Enabled = False
Dim Buttons As New List(Of Button) From {A3, A4, A5, A6, A7, B3, B4, B5, B6, B7, _
C3, C4, C5, C6, C7, D3, D4, D5, D6, D7, E3, E4, E5, E6, E7, F3, F4, F5, F6, F7}
For Each btn As Button In Buttons
If Me.CheckReservation(btn.Name) Then
btn.BackColor = Color.Firebrick
btn.Enabled = False
End If
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
If MsgBox("Are you sure you want to EXIT?", vbYesNo + vbQuestion, _
"Philippine Sweets Airline") = vbYes Then
End If
End Sub
End Class

And I don't know what the exact code to put on the line 14 to retrieve all 3 Tables.

Answer Source

You can use system tables to obtain list of tables:

SELECT Name FROM MSysObjects 
WHERE Type=1 AND Flags=0

More info: http://access.mvps.org/Access/queries/qry0002.htm