Visual Basic Online Course - MS Access 2003 Database Structure

Visual Basic Online Course
MS Access 2003 Database Structure

These are 2 methods on how to work with MS-Access 2003 Database components directly like (Tables & Queries) without opening your Data-Base file using ADO 2.8 .

1) Using OpenSchema
- Create new MS-Access 2003 DB (MyDb.mdb)
- Create VB6 Project
- Database (Fname Text 50,Lname Text 50, Nphone Text 50, Xemail Text 50)
- Place both in the same directory of course.
- Open your VB6 Project, from menu choose

(Project, References, Microsoft Ado 2.8 and Microsoft ADOx)

click to enlarge

- Visual Basic Code Snippets :

	'Visual Basoc 6.0 Lessons and Tricks
	Option Explicit
	Dim CN As New ADODB.Connection
	Dim RS As New ADODB.RecordSet
	Dim C0, C1, C2, C3 As String
	Private Sub CmdExit_Click()
	'Exit Application
	End
	End Sub
	Private Sub Combo1_click()
	'Connent to Table using Code, ADO 2.8
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	Set RS = CN.OpenSchema(adSchemaColumns, Array(Empty, Empty, Combo1.Text))
	'Listing all Columns from Database into Visual Basic 6.0 ListBox
	List1.Clear
	Do Until RS.EOF
	List1.AddItem RS!COLUMN_NAME
	RS.MoveNext
	Loop
	End Sub
	Private Sub Command1_Click()
	'AddNew \ Save
	If List1.ListCount > 0 Then
	C0 = List1.List(0)
	C1 = List1.List(1)
	C2 = List1.List(2)
	C3 = List1.List(3)
	CN.Execute ("Insert Into [" & Combo1.Text & "] ([" & C0 & "],[" & C1 & "],[" & C2 & "],[" & C3 & "]) " & _
	"VALUES ('" & TxtNm.Text & "','" & TxtLnm.Text & "','" & PhoneNo.Text & "','" & Txtmail.Text & "')")
	'OR you can use SQL insert into statement
	'CN.Execute ("Insert Into MyDearTable (Fname,Lname,Nphone,Xemail) VALUES & _
	('" & TxtNm.Text & "', '" & TxtLnm.Text & "', '" & PhoneNo.Text & "', '" & Txtmail.Text & "')")
	MsgBox "Updated"
	Command4_Click
	Else
	MsgBox "Please choose Table and Columns"
	Exit Sub
	End If
	End Sub
	Private Sub Command2_Click()
	'Search the database
	If List1.ListCount < 0 Then
	MsgBox ("Please choose table")
	Exit Sub
	End If
	If List1.ListIndex < 0 Then
	MsgBox ("Please choose Field")
	Exit Sub
	End If
	Dim SearchNm As String
	SearchNm = InputBox("Please choose something to search for")
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	RS.Open ("Select * From [" & Combo1.Text & "] Where [" & List1.Text & "] ='" & SearchNm & "'"), CN, adOpenDynamic, adLockOptimistic
	TxtNm.Text = RS(0)
	TxtLnm.Text = RS(1)
	PhoneNo.Text = RS(2)
	Txtmail.Text = RS(3)
	Set DG1.DataSource = RS
	DG1.Columns.Item("Fname").Caption = ("First Name")
	DG1.Columns.Item("Lname").Caption = ("Last Name")
	DG1.Columns.Item("Nphone").Caption = ("Phone No.")
	DG1.Columns.Item("Xemail").Caption = ("E-mail")
	DG1.Refresh
	End Sub
	Private Sub Command3_Click()
	'Delete Button
	If List1.ListCount < 0 Then
	MsgBox ("Please choose table")
	Exit Sub
	End If
	If List1.ListIndex < 0 Then
	MsgBox ("Please choose Field")
	Exit Sub
	End If
	Dim DelNm As String
	DelNm = InputBox("Please choose something to Delete")
	CN.Execute ("Delete From [" & Combo1.Text & "] Where [" & List1.Text & "] ='" & DelNm & "'")
	MsgBox ("OK")
	Command4_Click
	End Sub
	Private Sub Command4_Click()
	If Combo1.Text = Trim("") Then
	MsgBox "Please Choose Table"
	Exit Sub
	End If
	'Filling the DataGrid with Tables Names
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	RS.Open ("Select * From [" & Combo1.Text & "]"), CN, adOpenDynamic, adLockOptimistic
	Set DG1.DataSource = RS
	DG1.Columns.Item("Fname").Caption = ("First Name")
	DG1.Columns.Item("Lname").Caption = ("Last Name")
	DG1.Columns.Item("Nphone").Caption = ("Phone No.")
	DG1.Columns.Item("Xemail").Caption = ("E-mail")
	DG1.Refresh
	End Sub
	Private Sub Form_Load()
	If CN.State = 1 Then CN.Close
	CN.Open ("Provider = Microsoft.Jet.Oledb.4.0 ; Data Source = " & App.Path & "\MyDB.MDB")
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	Set RS = CN.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
	'Storing the Tables Names into a ComboBox
	Combo1.Clear
	Do Until RS.EOF
	Combo1.AddItem RS!Table_Name
	RS.MoveNext
	Loop
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Source Code - MediaFire.com Link

---

♥ Visual Basic Course Online :

• Automate MS-Access Database in Visual Basic 6.0 though a network
• Visual Basic 6.0 Analog Clock
• Visual Basic 6.0 Temperature Convertor
• Visual Basic 6.0, MS-Access 2003 and Crystal Reports - Phone book
• Visual Basic 6.0, MS-Access 2003 Database and DataGridView
• Visual Basic 6.0  FTP full application source code
• Visual Basic 6.0 Color Picker tool source code
• Visual Basic 6.0 TreeView Control lesson (Part1 - Part 2 - Part3 - Part4 - Part5) Arabic.
• Visual Basic 6.0 - Introduction to Graphics (Part1 - Part2 - Part3)  
• Visual Basic 6.0 and MS-Excel 2003 tutorials (Part1 - Part2 - Part3 - Part4 - Part5 - Part6)