Programming for Everybody: access

Showing posts with label access. Show all posts

Access database VBA programmer: Create insert update delete and search in another database with code

Private Sub Command12_Click()

If (Not IsNull(Me.idtxt.Value)) Then

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Select firstname,lastname,marks,image1 From Table6 " _

& " Where id= " & Me.idtxt.Value & ""

Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset(strsql)

If rst.EOF Then

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Else

Me.Image1.Picture = rst.Fields("image1")

Me.ftxt.Value = rst.Fields("firstname")

Me.ltxt.Value = rst.Fields("lastname")

Me.mtxt.Value = rst.Fields("marks")

End If

rst.Close

Else

MsgBox "Please Enter ID"

End If

End Sub

Private Sub Command13_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Update Table6 Set firstname='" & Me.ftxt.Value & "', " _

& "lastname = '" & Me.ltxt.Value & "',marks=" & Me.mtxt.Value & "," _

& "image1='" & Me.Image1.Picture & "' Where id= " & Me.idtxt.Value & ""

dbs.Execute (strsql)

MsgBox "Data Updated Successfully"

End Sub

Private Sub Command14_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Delete From Table6 Where id= " & Me.idtxt.Value & ""

dbs.Execute (strsql)

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Me.idtxt.Value = ""

MsgBox "Data Deleted Successfully"

End Sub

Private Sub Command7_Click()

Dim img_of As Office.FileDialog

Dim img_var As Variant

Set img_of = Application.FileDialog(msoFileDialogFilePicker)

img_of.Title = "Please Select image"

img_of.Filters.Clear

img_of.Filters.Add "Images", "*.png; *.jpg"

If img_of.Show = True Then

For Each img_var In img_of.SelectedItems

Me.Image1.Picture = img_var

Else

Me.Image1.Picture = ""

MsgBox "Please Select Image"

End If

End Sub

Private Sub Command8_Click()

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Me.idtxt.Value = ""

End Sub

Private Sub Command9_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Insert Into Table6(firstname,lastname,marks,image1) " _

& " Values('" & Me.ftxt.Value & "','" & Me.ltxt.Value & "'," _

& "" & Me.mtxt.Value & ",'" & Me.Image1.Picture & "')"

dbs.Execute (strsql)

MsgBox "Data Inserted Successfully"

End Sub

Visual basic.net: search data on datagridview using Textbox value by event TextChanged[ with code ]

Imports System.Data.OleDb

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\ddd.accdb")

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd1 As New OleDbCommand("select id,firstname,lastname,sum from table1 where firstname like '%" + TextBox1.Text + "%'", conn)

Dim da As New OleDbDataAdapter

Dim dt As New DataTable

da.SelectCommand = cmd1

dt.Clear()

da.Fill(dt)

DataGridView1.DataSource = dt

conn.Close()

End Sub

End Class

Microsoft Access - Relationships & Query(easy way) - ICDL

Types of Table Relationships in MS Access:

One – to – one relationship

The primary record will have only one related record.

One – to – Many relationships

The primary record may have many related records

Many– to – Many relationships

Many records from one table can be related to many records from another table through a junction table.

Login form access database vb.net

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\gamal\Pictures\aa.mdb")

Dim cmd As New OleDbCommand("select* from login1 where user1=@userl and pass1=@pass1", conn)

cmd.Parameters.Add("@userl", OleDbType.VarChar).Value = TextBox1.Text

cmd.Parameters.Add("@pass1", OleDbType.VarChar).Value = TextBox2.Text

Dim adapter1 As New OleDbDataAdapter(cmd)

Dim table As New DataTable

adapter1.Fill(table)

If table.Rows.Count <= 0 Then

MsgBox("Error username or password")

Else

MsgBox("login successfully")

End If

Download source

ربط فيجوال بيسك دوت نت بقاعدة بيانات أكسيس

' عايزين نعرف الاتصال بقاعدة البيانات

Imports System.Data

Imports System.Data.OleDb

Public Class Form2

'عايز نربط بقى بقاعدة بيانات أكسيس

Dim connection As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Users\gamal\Pictures\aa.mdb")

'عايزين نعرف مسار قاعدة البيانات

Dim index As Integer

Dim table As New DataTable

Public Sub loaddata(ByVal position As Integer)

Dim command As New OleDbCommand("select*from table1", connection)

Dim adapter As New OleDbDataAdapter(command)

adapter.Fill(table)

TextBox1.Text = table.Rows(position)(0).ToString()

TextBox2.Text = table.Rows(position)(1).ToString()

TextBox3.Text = table.Rows(position)(2).ToString()

TextBox5.Text = table.Rows(position)(3).ToString()

TextBox4.Text = table.Rows(position)(4).ToString()

End Sub

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

loaddata(index)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'لعرض أول سجل في قاعدة البيانات

index = 0

loaddata(index)

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

index += 1

loaddata(index)

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

index -= 1

loaddata(index)

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

index = table.Rows.Count() - 1

loaddata(index)

End Sub

End Class