Access #X021 ≪Excelへのクエリ出力で文字列が切り捨てられる不具合を回避する方法≫

ＡｃｃｅｓｓＴｉｐｓ by pPoy

←Top 　　　

Query　　

Form　　

Report　　

Sub　　

Function　　

Excel　　

Etc.　　

Word

Excel	Excelへのクエリ出力で文字列が切り捨てられる不具合を回避する方法（Access2010～Access2000）
Access から、クエリをExcel へ出力する場合、大抵は正しくすべてのデータが出力できます。ただし、Acceess のメモ型などの長い文字列を、クエリの式を使用して加工した結果を出力した場合、途中で切り捨てられてしまうことがあります。以下は、その不具合の詳細と回避方法です。詳細を飛ばして回避方法へのジャンプは、こちらです。

↑Top

≪不具合の例≫

出力するデータは、「tbl_pref」を使用します。

「note」フィールドは、「メモ型」となっています。
かなり長い文字列が入っています。

「tbl_pref」テーブルをもとに、「qry_pref_memo」クエリを作成します。

「メモ」列として、以下の式を入力します。
　　メモ: [pref_NM] & ":" & [note]

隣の列には、作成した「メモ」列の文字数を表示する式を入れてあります。

このクエリを実行した結果は、以下のようになります。
行の高さが足りない為、すべての文字が表示できていませんが、かなりの長さの文字列となっています。
クエリ実行結果

↑Top

ここで作成したクエリ「qry_pref_memo」を以下のコードを使用して、Excel に出力します。

Sub DAO2Excel2010()
On Error GoTo Err_DAO2Excel
'クエリの結果を、新規 Excel Book に出力
'Excel への参照設定はなし
'Access2010 → Excel2010 2011/01/22 pPoy
    Dim mdb As DAO.Database
    Dim rst As DAO.Recordset
    Dim stSQL As String         '出力するクエリ
    Dim stXLPath As String      '作成するファイルのフルパス
    Dim xls As Object           'Excel.Application
    Dim wkb As Object           'Excel.Workbook
    Dim i As Long
    
    stXLPath = "C:\Test\都道府県.xlsx"      '★Excel2010形式
    stSQL = "qry_pref_memo"
    Set mdb = CurrentDb
    
    'クエリオープン
    Set rst = mdb.OpenRecordset(stSQL)
    If Not rst.EOF Then
        'Excel の同名ファイルは容赦なく削除
        If Dir(stXLPath) <> "" Then
            Kill stXLPath
        End If
        
        'Excel Book 作成
        Set xls = CreateObject("Excel.Application")
        Set wkb = xls.Workbooks.Add
        '一枚目のシートに出力する
        With wkb.Worksheets(1)
            '項目行出力
            For i = 1 To rst.Fields.count
                .Cells(1, i).Value = rst.Fields(i - 1).Name
            Next
            'データ出力
            .Range("A2").CopyFromRecordset rst
        End With
        'ファイル保存後Excel終了
        wkb.SaveAs FileName:=stXLPath       
        xls.Visible = True
        xls.Quit
        Set wkb = Nothing: Set xls = Nothing
    End If
    MsgBox "出力終了！", vbOKOnly

Exit_DAO2Excel:
    Set rst = Nothing: Set mdb = Nothing
    Exit Sub
    
Err_DAO2Excel:
    MsgBox Err.Description
    Resume Exit_DAO2Excel

End Sub

↑Top

≪出力結果≫

作成された Excel ファイルを開くと、Ｄ列のメモ欄が怪しいことになっています。
怪しいデータ

←Ｆ列には、Excelで式を追加してあります。

元の文字数 (E列) と、Excel での文字数 (F列) が違っています。

しかも、D列の文字列末尾が文字化けしています。
結果は、２８５文字出力されているように見えますが、文字化けしている部分を除くと、
２５５文字しか出力できていないようです。

※ Access 2003 以前のデータベースから、 DAO や ADO を使用して出力した場合は、
きっちり２５５文字しか出力できません。怪しい文字列はついてきません。

≪原因≫

これは、Access のクエリでの式の結果が「テキスト型」として評価され、テキスト型の最大長である、
２５５文字に切りつめられることが原因のようです。

マイクロソフトの技術情報に似た事例が載っています。
[ACC2000] エクスポートしたクエリ式が 255 文字で切り詰められる
こちらの事例によると、手動でエクスポートした場合でも、切り捨てられてしまうようです。

↑Top

≪回避方法１≫

これを解消する方法も、上のリンク先に記載されています。
つまり、長い文字列を含むデータはクエリの式として作成するのではなく、一旦テーブル化してから出力することによって、正しくすべての文字列が出力できるのだそうです。

具体的な方法は以下のようにします。

先ほどの「qry_pref_memo」 クエリと同じ構造の新しいテーブルを作ります。
この時、「メモ」フィールドは、「メモ型」に設定します。
次に、「qry_pref_memo」を 追加クエリ に変更し、追加先は新しく作ったテーブルとします。
追加クエリ実行後、新しいテーブルからデータを出力する と、すべてのデータが出力できます。

※ 「qry_pref_memo」クエリを、テーブル作成クエリに変更しても、正しくデータは作成できません。
テーブル作成クエリで作られる「メモ」フィールドのデータ型は、「テキスト型」となってしまうため、
２５５文字以降のデータが切り捨てられてしまうからです。

↑Top

≪回避方法２≫

Access2010 で、DAO ではなく、ADO を使用して クエリを Excel に出力します。
最初に作成した選択クエリ「qry_pref_memo」を、以下のコードを使用して出力します。

Sub ADO2Excel2010()
On Error GoTo Err_ADO2Excel
'Access2010からクエリを出力し、Excel2010形式で保存する
'Excel, ADO への参照設定はなし 2011/01/22 pPoy
    Dim cnn As Object           'ADODB.Connection
    Dim rst As Object           'ADODB.Recordset
    Dim stSQL As String         '出力するクエリ
    
    Dim stXLPath As String      '作成するファイルのフルパス
    Dim xls As Object           'Excel.Application
    Dim wkb As Object           'Excel.Workbook
    Dim i As Long

    stXLPath = "c:\Test\都道府県ADO.xlsx"                   
    stSQL = "qry_pref_memo"
    Set cnn = CurrentProject.Connection
    Set rst = cnn.Execute(stSQL)
    
    If Not rst.EOF Then
        'Excel の同名ファイルは容赦なく削除
        If Dir(stXLPath) <> "" Then
            Kill stXLPath
        End If
        
        'Excel Book 作成
        Set xls = CreateObject("Excel.Application")
        Set wkb = xls.Workbooks.Add
        '一枚目のシートに出力する
        With wkb.Worksheets(1)
            '項目行出力
            For i = 1 To rst.Fields.count
                .Cells(1, i).Value = rst.Fields(i - 1).Name
            Next
            'データ出力
            .Range("A2").CopyFromRecordset rst
        End With
        'ファイル保存後 Excel終了
        wkb.SaveAs FileName:=stXLPath 
        xls.Visible = True
        xls.Quit
        Set wkb = Nothing: Set xls = Nothing
    End If
    MsgBox "出力終了！", vbOKOnly

Exit_ADO2Excel:
    Set rst = Nothing: Set cnn = Nothing
    Exit Sub
    
Err_ADO2Excel:
    MsgBox Err.Description
    Resume Exit_ADO2Excel
End Sub

≪結果≫

無事に、「メモ」欄の文字がすべて出力できます。
出力結果 ADO

≪補足≫

Access2010 のデータベースから ADO を使用した場合は、Excel97-2003形式で出力しても、
すべて正しく出力できます。
Access2010 では、DAO はダメで、ADO は大丈夫というのが、ちょっと腑に落ちませんが。
大体、規定で参照設定されているのは、DAO の方なのに。。。？？？
Access2003 以前のデータベースでは、 ADO を使用した場合、DAO と同様に 255 文字で
切れてしまいます。
※ Access2007 の場合は未検証です。
Access で「メモ型」のデータを扱う場合は、深くネストしたクエリを使用すると、文字が切れる場合があります。
また、文字化けする場合もあります。
Access でのメモ型は、単純なクエリで使用した方が安全です。
「メモ」欄に入力したデータは、ウィキペディアから、引用させていただきました。

↑Top