愚者の経験

「また今度」はほとんどこない

カテゴリーアーカイブ: ODBC

[Access][SQL Server]パススルークエリで「レコードが大きすぎます」のエラー

ODBCでパススルークエリ書いていると突然発生…。
どうやら文字列型のフィールドでの合計文字列数に上限があるみたいです。レコード長2000バイトみたいですね。
メモ型にするとこの制約から外れる(合計文字列数に含まれなくなる)んですが、パススルークエリって適当に型変換してますよね?

でもとりあえずnvarchar(max)にcastすればメモ型として見てくれる模様。
ODBCは意外な場所に落とし穴があって大変です。

[Access][ODBC][VBA]RefreshLinkとリンクテーブルマネージャでの再リンク

同じことやっているんだろう…そう思っていた時期が私にもありました。
Viewに適用するとRefreshLinkは主キーの擬似インデックスが外れますが、リンクテーブルマネージャは
外れません。なぜでしょう…これのせいでRefreshLinkがかなり使いづらいです。

[Access][ODBC]新規レコード確定時に入力データが別レコードに化ける

これを最初に見た時「ん?」となりました。以下の状況で発生します。
0.環境はSQL SERVER 2012または2008 R2とAccess2010のODBC接続(他でも起きるかはわかりません。確認出来たのはこの組み合わせ)
1.入力するテーブル[テーブルA]の主キーがidentityである
2.入力するテーブルのafter insertトリガーが設定してありその中でidentity列があるテーブル[テーブルB]にinsertを行う。
3.テーブルBの新規identityがテーブルAの主キーデータとして存在している
この状況で発生します。なんとなくこう書いただけでどうなるか検討がつく方は素晴らしいです。

テーブルAに新規にレコードを入力すると
1.テーブルA入力中は(新規)になっている(Bの次のidentityは1)
odbc1
odbc2
2.入力確定時にidentityを取得しに行く
3.入力確定時にテーブルBにinsertしているので取得するidentityはテーブルBのもの
4.テーブルAの(新規)の部分がテーブルBのidentityになりその値がテーブルAの主キーとしてデータが取得される
確定前
odbc3
確定後、レコードがidentity1が1のレコードに化ける(identity1に1が2行ある…)
odbc4
テーブルBは1が挿入されています。
odbc5
テーブルAはリクエリすると元に(?)戻る
odbc6

・回避方法
入力テーブルかトリガーのidentityを使わない…しかないのか?

テストテーブル

CREATE TABLE [dbo].[テーブルA](
	[identity1] [int] IDENTITY(1,1) NOT NULL,
	[value1] [int] NULL,
	[value2] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブルA] PRIMARY KEY CLUSTERED 
(
	[identity1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[テーブルB](
	[identity2] [int] IDENTITY(1,1) NOT NULL,
	[value] [int] NULL,
 CONSTRAINT [PK_テーブルB] PRIMARY KEY CLUSTERED 
(
	[identity2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

テストトリガー

CREATE TRIGGER [dbo].[utr_afiテーブルA]
   ON [dbo].[テーブルA]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	insert dbo.テーブルB(value)
	select 1

元に戻したいときはこれで
DBCC CHECKIDENT (テーブルB,RESEED ,0)

[Access]AccessでSQLiteを使用するメリット

SQLiteがAccess自身のデータベースと何が違うかというとやっぱり
・ファイルの容量制限がAccess(2GB)を超えられる
・トリガーが使える
この2点が大きくアドバンテージを得る部分でしょう。

他にも一応メリットになりそうなものは
・SQLが標準に近い(データベースサーバ移行時に再利用しやすい)
・使える構文、演算子がSQLiteのほうが豊富
・関数を追加可能(Cで書く必要が…)
こんな感じでしょうか。

ただデメリットもあり、
・リレーション(外部キー)がない(多分)
・ファイルサイズがAccessよりも大きい(約1.3倍)
・おなじみ通貨型(固定小数点型)がない(SQLiteの小数点型はよくわかりませんm(__)m)
等が挙げられます。

[Access]SQLiteをODBCで使用する件解決?

参考URL:http://kzworks.at.webry.info/200908/article_41.html
上記にSQLiteのODBCドライバを改造して公開されている方がいらっしゃいましたので
使わせていただきましたところちゃんとODBCリンク使えました。

SQL Server Compact EditionのODBCドライバありませんね~

[Access]SQLiteをODBCで使ってみようと思ったんですが…

昔の投稿に「ODBCは枯れつつある技術でこれからはADO」みたいに書いたんですが
実際Accessは2013からADPを使えませんので「やっぱりODBC」と思って
「これから開発するときはODBCか…じゃあAccessのDB使わずに他の組み込みDBで
最初からODBC接続で開発したほうがアップサイジングしやすいし…」
と思ってSQLiteのODBCドライバーがあったのでちょっとやってみたんですが…

ODBCドライバーをインストールしてDSNを作るまでは良かったんですが
リンクテーブルを作ろうとしたら「out of memory」「SQLSetConnectAttr」等が
エラーを吐いてどうしようもないです…知っている方がいたら教えてくださいm(__)m

ODBCリンクテーブルの一問一答

そもそもODBCつながらない…
→64bitだったらこれをやってみる。
なにかがおかしい…
→定義変更後にリンクテーブルの更新をした?
Viewのリンクテーブルでデータの追加ってどうするの?新規行がないよ?
→「CREATE INDEX <インデックス名> ON <テーブル名> (<列名(カンマ区切り)>) with primary」
を実行して擬似インデックスを作成。
更新、削除しようとすると競合エラーが出てできない…
→Null値が入ったbit型の列がない?
データがすべて#Deletedなんだけど…
→bigint型使ってたらdecimalとかmoneyとかに変更する。
値を消して移動しようとしたら「Variant型でない変数にNull値を代入しようとしました」と出る。保存前なのに…。
→NOT NULLの付く列はNULLにして移動しようとした瞬間に上記のエラーが発生します。
厄介なことに「どのイベントの発生よりも早い」ので回避出来ません。

とりあえず、エラーメッセージを見ても内容がわかりにくいものを厳選。

DSNが見つからない?

はい。知っていたのに忘れていました。

64bitの場合は「コントロールパネル」→「管理ツール」→「データソース(ODBC)」で
登録してもダメです…(システムドライブ)\sysWow64\odbcad32.exeからODBCを作成しましょう。

前者の操作で有効な場合もあります。
「登録したのに接続できない!」という場合に疑ってみてください。

Access2010でのODBCDirectの代替案

Access2013はAccessProject(ADP)がサポートされないのでほぼODBCを使うことになると思います。
しかしAccess2007以降ODBCDirectは使えません。

以下の文は実行時エラー3847
「ODBCDirectはサポートされません。DAOの代わりにADOを使用するには、
コードを記述しなおしてください。」と怒られます。

        CreateWorkspace("test", "", "", dbUseODBC)

ではADOでしか外部データベースに接続できないのかというとそうでもありません。
ODBCDirectの代わりにどうやって動的(コード)で接続するかというと
TableDef、QueryDefにある「Connect」プロパティを操作することで実現可能になります。

SQLコマンドパススルー発行関数

Public Function ServerExecute(SQLString As String, Connect As String, _
                Optional ReturnsRecords As Boolean = False, _
                Optional RecordsetType As DAO.RecordsetTypeEnum = dbOpenForwardOnly, _
                Optional RecordsetOption As DAO.RecordsetOptionEnum = dbReadOnly + dbSQLPassThrough, _
                Optional StoredReturn As Boolean = False) As DAO.Recordset

    With CurrentDb.CreateQueryDef("")
        .Connect = Connect
        .SQL = SQLString
        .ReturnsRecords = ReturnsRecords
        .ODBCTimeout = 0

        If (ReturnsRecords = True) Then
            Set ServerExecute = .OpenRecordset(RecordsetType, RecordsetOption)
        Else
            If (StoredReturn = True) Then
                .ReturnsRecords = True
                .SQL = "declare @ret int" & vbCrLf & _
                    "execute @ret=" & .SQL & vbCrLf & _
                    "select @ret as Ret"
                Set ServerExecute = .OpenRecordset(RecordsetType, RecordsetOption)
            Else
                .Execute
            End If
        End If
    End With
End Function

こんなかんじにすればDatabaseオブジェクトをオープンせずに
外部データベースにコマンドを発行、結果の取得ができます。

DAO.Recordsetをフォームの「Recordset」プロパティにセットする場合はRecordsetTypeをdbOpenSnapshotに指定しないと、実行にエラー(ナンバー7965)となり
「”Recordset/レコードセット”プロパティに、そのオブジェクトは使えません」が出ます。

またテーブルのリンクを貼り直すには以下のような感じで。

Public Function TablesRelink(ByVal Connect As String) As Boolean
On Error Resume Next
        Dim tdf As TableDef
        Dim pConn As String

        ' テーブルオブジェクトをループ
        For Each tdf In CurrentDb.TableDefs
                With tdf
                        ' Connectプロパティがあるかどうかでリンクテーブルかを調べる
                        If (Nz(.Connect, "")  "") Then
                                pConn = .Connect

                                ' プログラムの接続文字列代入
                                .Connect = Connect
                                .RefreshLink

                                If (Err.Number <> 0) Then
                                        ' エラーの場合は接続文字列を元に戻す
                                        .Connect = pConn
                                        Err.Clear

                                        ' 元の接続文字列でリンク確認
                                        .RefreshLink
                                        If (Err.Number <> 0) Then
                                                TablesRelink = True
                                                Err.Clear
                                        End If
                                End If
                        End If
                End With
        Next
End Function

複数のODBCでリンクしている場合は難しいですね。
これは内部ではJET経由になるのでしょうね。