【Access】テキストに載ってない便利Tips５選

作業用テーブルの準備する

 

 

Accessでは、更新できないクエリやSQLSERVERでのViewや、パススルークエリーなどから、テーブルを更新しようとすると、

 

 

となってエラーになることが多い。

 

そのために、作業用テーブルを作成して、そのテーブルから、更新するステップを入れなければならない場合がある。

以前は、こんなテーブルを沢山使っていたので、途中で、データが行方不明になって不可解な計算をしたりしたこともあるので、なるたけ使いたくなかったが、ほとんどのテーブルをリンクテーブルにして外部にしておくなら、リンクテーブルでないテーブルは作業用だとすぐにわかり、処理前には、データを消し丸ごと、他の更新できないクエリデータをコピーしている処理なのだと判別することができる。

ここでは、その前準備として、

する方法を記載する。

 

 

[php] Sub jyunbi() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim cn2 As New ADODB.Connection Dim rs2 As New ADODB.Recordset DoCmd.RunSQL "DELETE FROM [job_table]" ※この１行だけで、作業テーブルjob_tableテーブルの全レコードを消している。 Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset ※２つのレコードセットを準備 rs.Open "dbo_view_data", cn, adOpenKeyset, adLockOptimistic rs2.Open "job_table", cn2, adOpenKeyset, adLockOptimistic rs.MoveFirst Do Until rs.EOF rs2.AddNew rs2!JNO = rs!JNO rs2!HNO = rs!HNO rs2!計算数 = rs!計算数 rs2.Update rs.MoveNext Loop ※作業用テーブルjob_tableに、更新できないクエリdbo_view_dataの全レコードを追加している。 rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing rs2.Close: Set rs2 = Nothing cn2.Close: Set cn2 = Nothing End Sub [/php]

 

 

 

VBA内でSQL実行する書き方

 

 

Sub zaiko()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim SQL As String

On Error GoTo ErrRtn

Set cn = CurrentProject.Connection
cn.BeginTrans

If IsNull(call_在庫確認) Then
SQL = "UPDATE "
SQL = SQL & "dbo_stock_parts "
SQL = SQL & "INNER JOIN "
SQL = SQL & "job_product_parts_calc ON "
SQL = SQL & "(dbo_stock_parts.HNO = job_product_parts_calc.HNO AND "
SQL = SQL & "job_product_parts_calc.JNO = " & Me!JNO & ") "
SQL = SQL & "SET "
SQL = SQL & "在庫数 = 在庫数 + job_product_parts_calc.計算数"
Else
SQL = "UPDATE "
SQL = SQL & "dbo_stock_parts "
SQL = SQL & "INNER JOIN "
SQL = SQL & "job_product_parts_calc ON "
SQL = SQL & "(dbo_stock_parts.HNO = job_product_parts_calc.HNO AND "
SQL = SQL & "job_product_parts_calc.JNO = " & Me!JNO & ") "
SQL = SQL & "SET "
SQL = SQL & "在庫数 = 在庫数 - job_product_parts_calc.計算数"

End If

 

 

 

cmd.ActiveConnection = cn
cmd.CommandText = SQL
cmd.Execute

 

 

cn.CommitTrans

cn.Close: Set cn = Nothing

ExitErrRtn:
Exit Sub

ErrRtn:
MsgBox "エラー： " & Err.Description

cn.RollbackTrans
cn.Close: Set cn = Nothing

End Sub

 

 

 

特定文字に挟まれた文字列を抽出

 

 

たとえば、

201704221945-621009-2

の”-”で挟まれた６２１００９だけを抽出させたい場合。excelでは、find関数があるのに、アクセスにはない。

それを使えるように

 

にてFindxを使えるようにした。

 

 

 

これで、GaityuNumberは、抽出された数となる。

 

んんんってことで、

 

結論は、エクセルと並びが違うだけでした。

なので、Accessの場合は、

 

 

でハイフンの間の文字を抜くことができました。

 

 

 

自動で日付にフォームのテキストを付加する

 

 

 

JNOは、オートナンバーの同一フォーム上のテキストボックスなので、上記のVBAを動かすことで、自動で今日の日付にオートナンバーが付加されたものが代入される。

※テキストなので、＆をつかう。

 

 

ダブルクリック（２回クリック）禁止

 

 

ダブルクリック（２回クリック）を禁止したい時に使える。