- [VB.NET|Access] レコードの削除・挿入・更新（ODBC）: (2005/10/21)
- [VB.NET|Access] レコードの削除・挿入・更新（OleDb）: (2005/10/20)
- [VB.NET|Access] DataGrid に表示: (2005/10/14)
- [ASP.NET|Access] Access に接続: (2005/10/14)
- [VB6.0|Access] ADOで接続: (2005/10/11)
- [VB6.0|Access] DAOで接続: (2005/10/11)
[VB.NET|Access] レコードの削除・挿入・更新（ODBC）
Imports System.Data.Odbc
Private Conn As OdbcConnection
Private DA As OdbcDataAdapter
Private DS As DataSet
Private Sub frm_ODBC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Cmd_Select As OdbcCommand
Dim Cmd_Update As OdbcCommand
Dim Cmd_Delete As OdbcCommand
Dim Cmd_Insert As OdbcCommand
Dim strConn As String = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\テストDB.mdb"
Dim SQL_Select As String
Dim SQL_Update As String
Dim SQL_Insert As String
Dim SQL_Delete As String
Try
Conn = New OdbcConnection(strConn)
DA = New OdbcDataAdapter
'--- SELECT
SQL_Select = "SELECT * FROM TEST ORDER BY TESTFIELD1 ASC"
Cmd_Select = New OdbcCommand(SQL_Select, Conn)
DA.SelectCommand = Cmd_Select
'--- UPDATE
SQL_Update = "UPDATE TEST SET TESTFIELD2 = ? WHERE TESTFIELD1 = ?"
Cmd_Update = New OdbcCommand(SQL_Update, Conn)
Cmd_Update.Parameters.Add("@Field2", OdbcType.VarChar, 50, "TESTFIELD2")
Cmd_Update.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
DA.UpdateCommand = Cmd_Update
'--- DELETE
SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"
Cmd_Delete = New OdbcCommand(SQL_Delete, Conn)
Cmd_Delete.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
DA.DeleteCommand = Cmd_Delete
'--- INSERT
SQL_Insert = "INSERT INTO TEST VALUES (?, ?)"
Cmd_Insert = New OdbcCommand(SQL_Insert, Conn)
Cmd_Insert.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
Cmd_Insert.Parameters.Add("@Field2", OdbcType.VarChar, 50, "TESTFIELD2")
DA.InsertCommand = Cmd_Insert
'--- DataSet
DS = New DataSet
DA.Fill(DS, "TEST")
'--- Bind
'TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")
'TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")
DataGrid1.SetDataBinding(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
Finally
If Not IsNothing(Conn) Then Conn.Close()
End Try
End Sub
'--- 削除ボタン
Private Sub btnDELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDELETE.Click
Try
Dim Pos As Integer 'リスト内のポジション
Pos = BindingContext(DS, "TEST").Position
BindingContext(DS, "TEST").RemoveAt(Pos)
DA.DeleteCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 挿入ボタン
Private Sub btnINSERT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnINSERT.Click
Try
Dim Row As DataRow = DS.Tables("TEST").NewRow
Row(0) = CType(TextBox1.Text, Integer)
Row(1) = CType(TextBox2.Text, String)
DS.Tables("TEST").Rows.Add(Row)
DA.InsertCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.InsertCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 更新ボタン
Private Sub btnUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPDATE.Click
Try
Dim Row As DataRow
Dim RowCnt As Integer
RowCnt = 0
For Each Row In DS.Tables("TEST").Rows
If DS.Tables("TEST").Rows(RowCnt).Item(0) = CType(TextBox1.Text, Integer) Then
DS.Tables("TEST").Rows(RowCnt).Item(1) = CType(TextBox2.Text, String)
Exit For
End If
RowCnt = RowCnt + 1
Next
DA.UpdateCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.UpdateCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
2005 / 10 / 21
[VB.NET|Access] レコードの削除・挿入・更新（OleDb）
http://support.microsoft.com/kb/301248/
Imports System.Data.OleDb
Private Conn As OleDbConnection
Private DA As OleDbDataAdapter
Private DS As DataSet
Private Sub frm_OleDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Cmd_Select As OleDbCommand
Dim Cmd_Update As OleDbCommand
Dim Cmd_Delete As OleDbCommand
Dim Cmd_Insert As OleDbCommand
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\テストDB.mdb"
Dim SQL_Select As String
Dim SQL_Update As String
Dim SQL_Insert As String
Dim SQL_Delete As String
Try
Conn = New OleDbConnection(strConn)
DA = New OleDbDataAdapter
'--- SELECT
SQL_Select = "SELECT * FROM TEST ORDER BY TESTFIELD1 ASC"
Cmd_Select = New OleDbCommand(SQL_Select, Conn)
DA.SelectCommand = Cmd_Select
'--- UPDATE
SQL_Update = "UPDATE TEST SET TESTFIELD2 = ? WHERE TESTFIELD1 = ?"
Cmd_Update = New OleDbCommand(SQL_Update, Conn)
Cmd_Update.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")
Cmd_Update.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
DA.UpdateCommand = Cmd_Update
'--- DELETE
SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"
Cmd_Delete = New OleDbCommand(SQL_Delete, Conn)
Cmd_Delete.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
DA.DeleteCommand = Cmd_Delete
'--- INSERT
SQL_Insert = "INSERT INTO TEST VALUES (?, ?)"
Cmd_Insert = New OleDbCommand(SQL_Insert, Conn)
Cmd_Insert.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
Cmd_Insert.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")
DA.InsertCommand = Cmd_Insert
'--- DataSet
DS = New DataSet
DA.Fill(DS, "TEST")
'--- Bind
'TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")
'TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")
DataGrid1.SetDataBinding(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
Finally
If Not IsNothing(Conn) Then Conn.Close()
End Try
End Sub
'--- 削除ボタン
Private Sub btnDELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDELETE.Click
Try
Dim Pos As Integer 'リスト内のポジション
Pos = BindingContext(DS, "TEST").Position
BindingContext(DS, "TEST").RemoveAt(Pos)
DA.DeleteCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 挿入ボタン
Private Sub btnINSERT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnINSERT.Click
Try
Dim Row As DataRow = DS.Tables("TEST").NewRow
Row(0) = CType(TextBox1.Text, Integer)
Row(1) = CType(TextBox2.Text, String)
DS.Tables("TEST").Rows.Add(Row)
DA.InsertCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.InsertCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 更新ボタン
Private Sub btnUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPDATE.Click
Try
Dim Row As DataRow
Dim RowCnt As Integer
RowCnt = 0
For Each Row In DS.Tables("TEST").Rows
If DS.Tables("TEST").Rows(RowCnt).Item(0) = CType(TextBox1.Text, Integer) Then
DS.Tables("TEST").Rows(RowCnt).Item(1) = CType(TextBox2.Text, String)
Exit For
End If
RowCnt = RowCnt + 1
Next
DA.UpdateCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.UpdateCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
2005 / 10 / 20
[VB.NET|Access] DataGrid に表示
Imports System.Data.OleDb
'*** Form1
Dim Conn As OleDbConnection
Dim DA As OleDbDataAdapter
Dim DS As DataSet
Dim strSQL As String
Dim DBPath As String
Dim TableName As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try '--- DB接続 Try
'1行おきの背景色を設定する
DataGrid1.AlternatingBackColor = Color.FromArgb(120, 255, 255)
'DB接続設定
DBPath = "C:\YAMADA\テストDB.mdb"
TableName = "ユーザーマスタ"
strSQL = "SELECT * FROM UserMst"
'DB接続
Call JetConn.DB_Connect(Conn, DA, DS, strSQL, DBPath, TableName)
'データグリッドにテーブルを表示する
DataGrid1.SetDataBinding(DS, TableName)
'オブジェクトの開放
Call JetConn.DB_Cut(Conn, DA, DS)
Catch '--- DB接続 Catch
'オブジェクトの開放
Call JetConn.DB_Cut(Conn, DA, DS)
MessageBox.Show("値を取得できませんでした。", "エラータイトル")
End Try
End Sub
'*** JetConn.vb
'-----------------------------------------
' ■ DB_Connect ■ DB接続
' Conn = コネクションオブジェクト
' DA = データアダプタオブジェクト
' DS = データセットオブジェクト
' strSQL = SQL文
' mdbPath = Accessパス
' TableName = データセットに設定するテーブル名
'-----------------------------------------
Public Sub DB_Connect( _
ByRef Conn As OleDbConnection, _
ByRef DA As OleDbDataAdapter, _
ByRef DS As DataSet, _
ByVal strSQL As String, _
ByVal mdbPath As String, _
ByVal TableName As String)
Conn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & mdbPath)
DA = New OleDbDataAdapter(strSQL, Conn)
DS = New DataSet
DA.Fill(DS, TableName)
End Sub
'-----------------------------------------
' ■ DB_Connect ■ DB切断
' Conn = コネクションオブジェクト
' DA = データアダプタオブジェクト
' DS = データセットオブジェクト
'-----------------------------------------
Public Sub DB_Cut( _
ByRef Conn As OleDbConnection, _
ByRef DA As OleDbDataAdapter, _
ByRef DS As DataSet)
If Not DS Is Nothing Then DS.Dispose()
If Not DA Is Nothing Then DA.Dispose()
If Not Conn Is Nothing Then Conn.Dispose()
End Sub
2005 / 10 / 14
[ASP.NET|Access] Access に接続
Dim Conn As New OleDbConnection
Dim Cmd As New OleDbCommand
'DB接続文字列の設定
'注：プロジェクトファイルホルダの下にあるbinホルダにMDBを置く
'System.AppDomain.CurrentDomain.BaseDirectory = App.Path
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
System.AppDomain.CurrentDomain.BaseDirectory & "\bin\***.mdb"
'コネクションの設定
Cmd.Connection = Conn
'DB接続
Conn.Open()
'SQL文の設定
strSQL = "SELECT * FROM BookMark ORDER BY SiteNo"
Cmd.CommandText = strSQL
DS.Dispose()
DA.Dispose()
Cmd.Dispose()
Conn.Dispose()
2005 / 10 / 14
[VB6.0|Access] ADOで接続
'事前バインディングの場合（参照設定：Microsoft ActiveX Data Objects 2.7 Library） Dim AdoConn As New ADODB.Connection Dim AdoRS As New ADODB.RecordsetAdoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sample.mdb"
AdoRS.Open [SQL文かテーブル名], AdoConn
'実行時バインディングの場合（参照設定：なし） Dim AdoConn As Object Dim AdoRS As ObjectSet AdoConn = CreateObject("ADODB.Connection")
Set AdoRS = CreateObject("adodb.recordset")AdoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sample.mdb"
AdoRS.Open [SQL文かテーブル名], AdoConn
'終了処理
AdoRS.Close
Set AdoRS = Nothing
AdoConn.Close
Set AdoConn = Nothing
2005 / 10 / 11
[VB6.0|Access] DAOで接続
'事前バインディングの場合（参照設定：Microsoft DAO 3.6 Object Library） Dim DaoWS As DAO.Workspace Dim DaoDB As DAO.Database Dim DaoRS As DAO.RecordsetSet DaoWS = DBEngine.Workspaces(0)
Set DaoDB = DaoWS.OpenDatabase("C:\sample.mdb")
Set DaoRS = DaoDB.OpenRecordset([SQL文かテーブル名], dbOpenDynaset)
'終了処理
DaoRS.Close
Set DaoRS = Nothing
DaoDB.Close
Set DaoDB = Nothing
DaoWS.Close
Set DaoWS = Nothing
2005 / 10 / 11