パソコン(ソフト)

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

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

 

 

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

 

更新できるクエリじゃありません

 

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

 

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


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

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

作業用テーブル内のレコード全消し、更新できないクエリデータ全コピー

する方法を記載する。

 

 

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

 

 

 

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

 

 

 

-パソコン(ソフト)
-, , ,

Copyright© PCTips , 2019 All Rights Reserved.