パソコン情報

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

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

 

 

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]" ※この1行だけで、作業テーブルjob_tableテーブルの全レコードを消している。 Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset ※2つのレコードセットを準備 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

 


上記の形式でVBAの中にSQLを書き込んでいけばOK

しかし、半角スペースが分かりずらいので、実際にイミディエイトウインドウ内で確認してみて気づくこともあった。

  ※Debug.Print SQL

イミディエイトウインドウに表示された内容。

UPDATE dbo_stock_parts INNER JOIN job_product_parts_calc ON (dbo_stock_parts.HNO = job_product_parts_calc.HNO AND job_product_parts_calc.JNO = 9) SET 在庫数 = 在庫数 + job_product_parts_calc.計算数

 

 

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

 

上記の3行でSQLを実行させている

 

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

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

それを使えるように

Function Findx(moji1, moji2, kaishi)
Findx = InStr(kaishi, moji2, moji1)
End Function

 

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

 

Samplestring = Me!select_外注手配番号.Value EndString = InStr(14, Samplestring, "-") GaityuNumber = Mid(Samplestring, Findx("-", Samplestring, 1) + 1, Findx("-", Samplestring, Findx("-", Samplestring, 1) + 1) - (Findx("-", Samplestring, 1) + 1))

 

 

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

 

んんんってことで、

Find関数と、AccessのInStr関数は、並びが違うだけ?

 

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

なので、Accessの場合は、

 

Mid(Samplestring, InStr(1, Samplestring, "-") + 1, InStr(InStr(1, Samplestring, "-") + 1, Samplestring, "-") - (InStr(1, Samplestring, "-") + 1))

 

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

 

 

 

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

 

 

Me.日付 = Now() Me.受注番号 = Format(Now(), "yyyymmdd") & Me!JNO

 

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

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

 

 

ダブルクリック(2回クリック)禁止

 

 

ダブルクリック(2回クリック)を禁止したい時に使える。

 

Private Sub テスト_Click()

Static Boo As Boolean

If Boo Then MsgBox "起動中に2回クリックはできません。"

Exit Sub

Else

######## ここに処理させたいプログラム ########

Boo = True

End If

End Sub