VB2019でaccess(accdb)に接続して参照・更新させたい
投稿者 ロト君  (社会人)
投稿日時
2021/6/19 15:31:00
問題は自分で解決出来ました!
参考になったURLを添付致します。
ひろにもブログ
https://hironimo.com/prog/vbnet/vb-net-accessdb-upd/
コードは下記のモノです。↓
===================
Imports System.Data.OleDb
Public Class Form1
Dim Recode_id As Long = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'SQL作成
Dim resultDt As New DataTable
Dim sql = New System.Text.StringBuilder()
sql.AppendLine("SELECT")
sql.AppendLine(" *")
sql.AppendLine("FROM t_data")
'Access接続準備
Dim command As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim cnAccess As OleDbConnection = New OleDbConnection
cnAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\data.accdb"
'Access接続開始
cnAccess.Open()
Try
command.Connection = cnAccess
command.CommandText = sql.ToString
da.SelectCommand = command
'SQL実行 結果をデータテーブルに格納
da.Fill(resultDt)
Catch ex As Exception
Throw
Finally
command.Dispose()
da.Dispose()
cnAccess.Close()
End Try
'データテーブルの結果を表示
For rowindex As Integer = 0 To resultDt.Rows.Count - 1
For colindex As Integer = 0 To resultDt.Columns.Count - 1
Console.Write(resultDt.Rows(rowindex).Item(colindex).ToString & " ")
Next
Console.WriteLine()
Next
TextBox1.Text = resultDt.Rows(0).Item(1).ToString
Recode_id = resultDt.Rows(0).Item(0).ToString
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'SQL作成
Dim sql = New System.Text.StringBuilder()
sql.AppendLine("UPDATE t_data")
sql.AppendLine("SET code = '" & TextBox1.Text & "'")
sql.AppendLine("WHERE ID = " & Recode_id)
'Access接続準備
Dim command As New OleDbCommand
Dim cnAccess As OleDbConnection = New OleDbConnection
cnAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\data.accdb"
'Access接続開始
cnAccess.Open()
Dim tran As OleDbTransaction
tran = cnAccess.BeginTransaction
Try
command.Connection = cnAccess
command.Transaction = tran
command.CommandText = sql.ToString
command.ExecuteNonQuery()
tran.Commit()
Catch ex As Exception
tran.Rollback()
Throw
Finally
command.Dispose()
cnAccess.Close()
End Try
End Sub
End Class
===================
お手数、おかけしました!
参考になったURLを添付致します。
ひろにもブログ
https://hironimo.com/prog/vbnet/vb-net-accessdb-upd/
コードは下記のモノです。↓
===================
Imports System.Data.OleDb
Public Class Form1
Dim Recode_id As Long = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'SQL作成
Dim resultDt As New DataTable
Dim sql = New System.Text.StringBuilder()
sql.AppendLine("SELECT")
sql.AppendLine(" *")
sql.AppendLine("FROM t_data")
'Access接続準備
Dim command As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim cnAccess As OleDbConnection = New OleDbConnection
cnAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\data.accdb"
'Access接続開始
cnAccess.Open()
Try
command.Connection = cnAccess
command.CommandText = sql.ToString
da.SelectCommand = command
'SQL実行 結果をデータテーブルに格納
da.Fill(resultDt)
Catch ex As Exception
Throw
Finally
command.Dispose()
da.Dispose()
cnAccess.Close()
End Try
'データテーブルの結果を表示
For rowindex As Integer = 0 To resultDt.Rows.Count - 1
For colindex As Integer = 0 To resultDt.Columns.Count - 1
Console.Write(resultDt.Rows(rowindex).Item(colindex).ToString & " ")
Next
Console.WriteLine()
Next
TextBox1.Text = resultDt.Rows(0).Item(1).ToString
Recode_id = resultDt.Rows(0).Item(0).ToString
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'SQL作成
Dim sql = New System.Text.StringBuilder()
sql.AppendLine("UPDATE t_data")
sql.AppendLine("SET code = '" & TextBox1.Text & "'")
sql.AppendLine("WHERE ID = " & Recode_id)
'Access接続準備
Dim command As New OleDbCommand
Dim cnAccess As OleDbConnection = New OleDbConnection
cnAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\data.accdb"
'Access接続開始
cnAccess.Open()
Dim tran As OleDbTransaction
tran = cnAccess.BeginTransaction
Try
command.Connection = cnAccess
command.Transaction = tran
command.CommandText = sql.ToString
command.ExecuteNonQuery()
tran.Commit()
Catch ex As Exception
tran.Rollback()
Throw
Finally
command.Dispose()
cnAccess.Close()
End Try
End Sub
End Class
===================
お手数、おかけしました!
VB6で作っていたころは32bit環境でACCESS2000(mdb)を使ってソフトが出来ていましたが、新しいバージョンでの作り方が分からない次第です。
COMの参照設定や、imports等々どうすればよいでしょうか?
また、OSは64bitを使っています。