突貫工事で作ったのでバグは多少あると思いますが{例えばsql インジェクション対策とか。}、Accessのsampleコードができましたので貼っときます。なお、これで何が出来るかって言えばDBの修正、追加、削除といった基本的な動きをします{挙動不審ぎみ嘘!?}。若干、ソースコードを弄くれば使い回しが出来るようにしております。飯くったり他のことをしながらもガリガリとコードを書きました。ちなみにAccessのViewerをマイクロソフトからダウンロードすると動作すると思います。ファイルは下記からダウンロードください。 追記:暇な時に改良していきますので( ゚д゚)ノ ヨロシクとコメントも暇があれば書いときます。そして無駄なコードがありますが、それも軽量していくつもりです(`・ω・´)。ちなみにいまはDBを空にすると落ちますので・・・・(直しました。)。?samplezip358.zip?[https://zip358.com/tool/samplezip358.zip][08/21 11:26]
Option Compare Database Public db_x As Long Public max As Variant Function sel_all(tablename As Variant, tal_valname As Variant) As Variant Dim x As Long Dim y As Long Dim sql As String Dim RS As DAO.Recordset Dim db As DAO.Database Dim hoge(999, 999) As Variant Dim val_n As Variant Dim val As Variant Set db = CurrentDb sql = "SELECT * FROM " & tablename & " ORDER BY ID ASC;" MsgBox sql Set RS = db.OpenRecordset(sql) Do Until RS.EOF For y = 0 To UBound(tal_valname) hoge(x, y) = RS.Fields(tal_valname(y)) Next y RS.MoveNext x = x + 1 Loop Set db = CurrentDb sql = "SELECT MAX(ID) as maxs FROM " & tablename & ";" MsgBox sql Set RS = db.OpenRecordset(sql) max = RS.Fields("maxs") db_x = x - 1 Set db = Nothing sel_all = hoge End Function Function up_in(chk As Boolean, tablename As Variant, tal_valname As Variant, tal_val As Variant, ID As Long) As Variant Dim sql As String Dim RS As DAO.Recordset Dim db As DAO.Database Dim hoge_valname As String Dim hoge_val As String Dim hoge As Variant If chk = True Then For i = 0 To UBound(tal_valname) hoge_valname = hoge_valname & tal_valname(i) & ", " Next i hoge_val = sql_str(tal_val, "", "", True) sql = "INSERT INTO " & tablename & " (" & Left(hoge_valname, Len(hoge_valname) - 2) & ")VALUES (" & hoge_val & ");" Else hoge_valn_val = sql_str("", tal_valname, tal_val, False) sql = "Update " & tablename & " Set " & hoge_valn_val & " WHERE ID = " & ID & ";" End If MsgBox sql Set db = CurrentDb db.Execute (sql) Set db = Nothing up_in = True End Function Function del(tablename As Variant, tal_valname As Variant, tal_val As Variant) As Variant Dim sql As String Dim RS As DAO.Recordset Dim db As DAO.Database Dim hoge As Variant sql = "DELETE FROM " & tablename & " WHERE " & tal_valname & " = " & tal_val & ";" MsgBox sql Set db = CurrentDb db.Execute (sql) Set db = Nothing del = True End Function Function sql_str(hoge As Variant, tal_valname As Variant, tal_val As Variant, chk As Boolean) As Variant Dim i As Long Dim hoge_str As Variant If chk = True Then For i = 0 To UBound(hoge) If IsNumeric(hoge(i)) Then hoge_str = hoge_str & hoge(i) & ", " Else hoge_str = hoge_str & "'" & hoge(i) & "', " End If Next i Else For i = 0 To UBound(tal_valname) If IsNumeric(tal_val(i)) Then hoge_str = hoge_str & tal_valname(i) & " = " & tal_val(i) & ", " Else hoge_str = hoge_str & tal_valname(i) & " = '" & tal_val(i) & "', " End If Next i End If sql_str = Left(hoge_str, Len(hoge_str) - 2) End Function
↑クラスdbソースコード
Option Compare Database Dim max As Long Dim ID As Long Dim val_val As Variant Private Sub Form_Load() lod End Sub Sub lod() Dim db As db_db Dim val_name As Variant Dim x As Long Set db = New db_db val_name = Array("ID", "表題", "数値", "文字") val_val = db.sel_all("tableone", val_name) max = db.max + 1 If cmb.ListCount > 0 Then For x = 0 To cmb.ListCount - 1 cmb.RemoveItem 0 Next End If For x = 0 To db.db_x cmb.AddItem val_val(x, 1) Next Set db = Nothing End Sub Private Sub cmb_Click() If cmb.ListIndex >= 0 Then Viw cmb.ListIndex End If End Sub Private Sub del_btn_Click() Dim db As db_db Dim hoge As Variant Set db = New db_db val_name = Array("ID", "表題", "数値", "文字") If ID > 0 And max > 1 Then hoge = db.del("tableone", "ID", ID) End If Set db = Nothing lod End Sub Private Sub in_btn_Click() Dim db As db_db Dim val_name As Variant Dim val As Variant Dim hoge As Variant chkchk val_name = Array("ID", "表題", "数値", "文字") val = Array(max, cmb, suuzi, moji) Set db = New db_db hoge = db.up_in(True, "tableone", val_name, val, max) Set db = Nothing lod End Sub Private Sub upd_btn_Click() Dim db As db_db Dim val_name As Variant Dim val As Variant Dim hoge As Variant chkchk val_name = Array("表題", "数値", "文字") val = Array(cmb, suuzi, moji) Set db = New db_db If ID > 0 And max > 1 Then hoge = db.up_in(False, "tableone", val_name, val, ID) End If Set db = Nothing lod End Sub Sub Viw(i As Long) ID = val_val(i, 0) suuzi = val_val(i, 2) moji = val_val(i, 3) End Sub Sub chkchk() If IsNumeric(suuzi) Then If suuzi > 9999 Then suuzi = 9999 End If Else suuzi = 0 End If If IsNumeric(moji) Then moji = "文字が不正>" & moji End If If IsNumeric(cmb) Then cmb = "文字が不正>" & cmb End If End Sub
↑formソースコード