SQL SERVER + MS Access + ストアド プロシージャ

今回はSQL SERVERのストアドプロシージャを手軽に使う方法です。
あまり利用するケースが少ないですが、レアケースがあった場合に便利です。

プログラミング的なことをストアドプロシージャでやろうとすると結構面倒くさいので、今回はVBA(MS Access)側でSQL SERVER向けのSQLを生成し、SQL SERVER側でSQLを処理する形です。

ストアドプロシージャーの記述

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[svr_query]
	@sql text
AS
BEGIN
	exec (@sql)
END

VBAの記述

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim tb As DAO.Recordset
Dim sql as String

sql = "select * from dbo.TEST where COL_A='サンプルテキスト';"
sql = Replace(sql, "'", "''")

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("", False, False, "ODBC;DSN=●●●●;UID=●●●;PWD=●●●;")
Set tb = db.OpenRecordset("EXECUTE svr_query '" & sql & "'", dbOpenSnapshot, dbSQLPassThrough)

If tb.RecordCount > 0 Then
    Do Until tb.EOF
        *****取得したデータの処理*****
        tb.MoveNext
    Loop
End If

※途中、sqlのシングルクォーテーションを二重に置換してますが、こうしないとSQL SERVER側でSQLが実行されません。
また、この方法を用いることで、データベースが別の場合もシンプルなSQLで対応することが可能です。

UWSC

いろいろなマクロツールをそれほど試したわけでは無いですが、

UWSCがほんとにオススメです。

マクロサンプルページを見れば機能の豊富さが一目瞭然ですが、

今回使った内容は、単純な入力作業の自動化です。

といっても完全なる自動化ではないですが。

 

例えば、基幹システムの入力インターフェイスが

独自で作成されたものであれば、無駄な項目もなく

スムーズに入力業務が行えると思いますが、

大体の会社は汎用的なシステムを導入していることが多く、

入力に無駄な項目があることで、かなりロスになります。

 

もちろん、不要項目を除去できたり、

例えばCSVで一括でインポート出来たりも機能としてはあるでしょうが、

どうしてもパートなどの手を使って入力する場合の効率化です。

 

独自インターフェイスよりも、やはりエクセルが触りやすいでしょう。

お客さんからメールで発注が来た場合などにも有効です。

コピペすれば良いだけですからね。

ということで、エクセルに入力してもらい、

マクロを掃出し、UWSCで自動入力

 

無駄な項目であったり、毎回同じ数字を入れる必要があるところは、

マクロ作成時に付与してあげればよいのです。

excel

入力する項目以外はロックを掛ければ、比較的安定して動作します。

 

エクセルベースな開発、ご要望ございましたらお気軽にお問い合わせください。