プログラマーのメモ書き

伊勢在住のプログラマーが気になることを気ままにメモったブログです

Access -> SQL Server への移行 (2/2)

さて、前記事でバックエンド側のテーブルを SQL Server に移行しました。SQL Server Management Studio で接続してテーブルを見てみると、特に問題なく移行できているように思えます。

次は、フロントエンド側のAccessから、この SQL Server に接続して今までと同じ操作ができるように整備します。

リンクテーブルの作成

まず初めに、今までバックエンドAccessファイルにあるテーブルに対してリンクテーブルを張っていたのを、 SQL Server 上のテーブルに張るように変更します。

フロントエンド側のAccessファイルを開き、既存のリンクテーブルを削除します。 次に、 SQL Server へのリンクテーブルを作成するのですが、Access の機能(メニューの『外部データ』→『ODBCデータベース』)を使うと、DSNを作らないといけません。今回はこのフロントエンドAccessファイルを複数人に配布して使うので、DSNを作成するのは(設定作業が増えるので)避けたいと思います。

ということで、今回は DSN-less 接続 というのを試しました。

DSN-less 接続

DSNを作成しなくても、リンクテーブルを作成する方法です。VBAを使い、接続文字列を自前で用意し、CreateTableDef メソッドでリンクテーブルを作成するという方法です。

https://support.microsoft.com/ja-jp/kb/892490

http://vba-geek.jp/blog-entry-83.html

https://foolexp.wordpress.com/2012/06/14/access%E3%81%A7dns%E3%81%AA%E3%81%97%E3%81%A7odbc%E6%8E%A5%E7%B6%9A/

マイクロソフトさんのサイトにあるサンプルを改良して設定できるようにしました。ちなみに、下記の様な接続文字列を定義しました。

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=サーバー名;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード

ドライバーとして SQL Server ではなく SQL Server native Client 11.0 を使っていますが、これは後述する理由のためです。

リンクテーブルが作成されて、移行時のデータが表示できれば問題なしです。

ドライバによる不具合

実は、最初はリンクテーブルを

ODBC;DRIVER=SQL Server;SERVER=サーバー名;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード

としてドライバに SQL Server を使って設定していました。

当初はこれで問題なかったのですが、作業途中でサーバー名を変更することがあり、同じやり方でリンクテーブルを張りなおしたら、なぜか日付型のフィールドが文字型として認識されるという状態になってしまいました。何度リンクテーブルを張りなおしても改善されず、リンクテーブルなのでフィールドの型を変更することもできず困ってしまいました。

調べてみると、 SQL Server 側で新しい日付型 (datetime2)を使っているとき、ドライバが SQL Server だとこのような現象が発生するとのことでした。SSMAで移行する場合、Accessの日付型のフィールドが SQL Server 側で datetime2 にマッピングされているため発生した次第です。

解決方法は、 SQL Server Native Client 11.0 を使えばよいとのことです。

MS-Access sees SQL server's datetime2 fields as TEXT - Stack Overflow

SQL Server Native Client のインストール

で、実際に試してみると問題なく日付型として認識できました。

ただ、一点疑問が残るのは、なぜ、最初にリンクテーブルを張った際は正しく日付型を認識できていたのか?という点です。まあ、頑張って再現させても得るものはなさそうですし、とりあえず問題が解決したのでこれで良しとします。

調整

さて、リンクテーブルのリンク先が切り替わっただけなので、修正なしで使えるかと思いきや、あれこれエラーが発生します。 今回対応したもので覚えているのを書いておきます。

3622 エラーが表示される

フロントエンドAccessを開いて、適当なテーブルの中身を表示させると下記の様なエラーが表示されました。

f:id:junichim:20161111164045p:plain

これへの対応としては、読み取り専用の場合は dbOpenSnapshot を指定し、書き込みを行う場合は dbOpenDynaset とオプションに dbSeeChanges を指定しました。

sql - Errors with linked tables and Ms Access ( Run-time error '3622' : dbSeeChanges/Identity column ) - Stack Overflow

DAOプロパティについて --DAO、ADO、SQL & Access フォーラム--

SQL Server の check制約 の内容が不正

SSMA による移行では、Access テーブルに設定してあった入力規則が SQL Server 上のテーブルに対する Check 制約に変換されました。このとき、Access側の記述をそのまま SQL Server 側に当てはめているようで書式が一致しないため、エラーになる場合があります。

具体的には、

like "####"

という4桁の数値という入力規則ですが、 SQL Server 側はこれを認識してくれません。

ということで、これを

like N'[0-9][0-9][0-9][0-9]'

と書き直しました。

DAO 経由でレコードを AddNew で追加しようとする場合、ID(主キー、オートインクリメント)が正しく取得できない

リンクテーブルの接続先がAccessファイルの場合は、AddNewを実行後、update前でも、オートインクリメントのフィールドの値を取得することができました。 でも、SQL Server のリンクテーブルの場合、updateを呼ぶ前だと取得できませんでした(まあ、考えてみれば当たり前ですよね)。

ということで、下記サイトを参考に、いったんupdate実行後、追加したレコードのオートインクリメントフィールドの値を取得するようにしました。

Autonumber value of last inserted row - MS Access / VBA - Stack Overflow

日本語で検索するSQL

フロントエンド側でSQLを組み立ててそのまま実行すると、なぜかうまく結果を取得することができませんでした。 例えば、下記の様な場合です。

Set rs = db.OpenRecordset("select ID from テーブル名 where 名前 = '" & person & "'", dbOpenSnapshot)

SQL Server では文字はUnicodeで扱っているようですが、Access(というよりVBAの開発環境のVBE)がUnicodeに対応していないようです。 幸い、このような処理を行っている箇所は少なかったので、パススルークエリを利用して、SQL Server 側で処理するようにしました。

    Dim query As String
    query = "select id from テーブル名 where カラム名 = N'検索文字列'"

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    
    qdf.Connect = DbLinkUtil.getConnStringToSQLServer
    qdf.ReturnsRecords = True
    qdf.sql = query
    
    Set rs = qdf.OpenRecordset(, dbOpenSnapshot)
    qdf.Close
    Set qdf = Nothing

これで、得られたレコードセットに対して処理を行いました。

(参考)

当初は、下記リンク先を参考にして、strConvをかますように変更していました。

Set rs = db.OpenRecordset("select ID from テーブル名 where strconv([名前], 64) = '" & StrConv(worker, vbUnicode) & "'", dbOpenSnapshot)

※ 名前フィールドに対するstrconvの64はvbUnicodeのことです

Finding unicode characters in SQL Search

しかし、この方法の場合、場合によっては変換後の文字列に『(』が含まれ、正しいクエリ文字として解釈されないケースがあったため、採用しませんでした。

フォームで新規レコード作成時、規定値が反映されない

Accessファイルへのリンクテーブルを使って、リンク先のテーブルに規定値が設定されている場合は、フォームで新規レコードを作成した際に、規定値が反映されていました。 SSMA により SQL Server へ移行すると、規定値は、default制約の形で反映されます。 しかし、SQL Server へのリンクテーブルだと、フォームで新規レコードを作成した際に、この default制約が反映されません。

これも仕方ないと思うので、フォーム側を修正して、規定値を反映できるようにしました。

最後に

以外と修正箇所が多かったのですが、ここまで修正したところ、エラーをはかずに動くようになりました。

あとは、バックエンドとしてAccessファイルを使っている場合は処理速度的に問題がなかったところが、SQL Server へのリンクテーブルだと問題になるところがいくつかありました。 まあ、構造が変わっているので、仕方ないですね。

これらは SQL server へのアクセスを考慮して、効率的な処理に書き直したり、 SQL Server 側でのユーザー定義関数やストアドプロシージャを使った処理に順次書き直していきました。

一応これで動くようになりました。ふー。 しばらく運用してみて、問題があれば、随時対応していきたいと思います。