Accessのリンクテーブルで特定の列が#Deletedと表示される
Accessはほとんど使わないので、どういう事象なのかまったくわからない
調べてみると、原因となる可能性が多すぎる…
今回は解決できた(2023/12/15)
環境
Windows 10
Microsoft Access(Office 2016)
SQL Server 14.0.1000.169
起こった事象
AccessのリンクテーブルでSQL Serverのテーブルを参照すると特定の列が#Deletedで表示されるようになった
同名のテーブルで以前は正常に表示されていた、あるときを境に#Deletedになった
正常パターンとエラーパターン
1. 正常に見れていたとき
初めにSQL Serverで作ったテーブル
code:sql
id int,
name nvarchar(8),
created_at datetime2,
PRIMARY KEY(id)
);
select * from #SAMPLE;で問い合わせると正常に取得できる
table:result
id name created_at
1 hoge 2023-12-01...
このテーブルにAccessからODBC経由でリンクテーブルを作成すると、正常に全レコードが表示される
table:access
id name created_at
1 hoge 2023-12-01...
ここまではOK
2. Accessで#Deletedになったとき
テーブルの主キーをidのみからidとcreated_atに変更した(dropしてcreateしなおした)
code:sql
id int,
name nvarchar(8),
created_at datetime2,
PRIMARY KEY(id, created_at)
);
select * from #SAMPLE;の問い合わせると、変更前と変わらず正しく取得できる
table:result
id name created_at
1 hoge 2023-12-01...
このテーブルにAccessからODBC経由でリンクテーブルを作成すると、created_at列がすべて#Deletedになった
table:access
id name created_at
原因と考えられるもの
公式ドキュメントによると、日付(datetime, datetime2)まわりが怪しい
症状
原因
この問題は、datetime列またはdatetime2列に秒の小数部の値が含まれているために発生します。Microsoft SQL Server 2016以降、datetime2型に対する秒の小数部の処理方法が変更されました。変更の詳細については、次の記事を参照してください。(略) 注意
解決策
これが無理なら以下のような回避策がある
2. datetime列とdateTime2列が主キーの一部ではないことを確認します。
4. UPDATEステートメントを使用して、datetime列から秒の小数部を削除します。
5. データ型をdatetimeからsmalldatetimeに変更します。注:これにより、値が最も近い分に丸められます。
6. データを編集する必要がない場合は、クエリを作成し、RecordsetTypeプロパティをSnapshotに変更して、この問題の影響を受けない読み取り専用レコードセットを返します。
ということでcreated_at以外を主キーに使うことで#Deletedにならなくなった
2.以外の解決方法はリスクちょっと高い…
今回は解決したが…
調べていると、日付以外でもBigint型の列があると起こり得るという事象があった
Bigintは使っていなかったので検索対象から外していたんだけど、これも#Deletedになるっぽい
環境としてはOffice 2016頃が多いみたい
これも最新なら解決しているのかな？