MS Access创建表正在截断备注字段

Access 2013

我有几个使用宏编写的UNION ALL查询合并的日志。

结果查询如下：

SELECT [zData Navy FY15 AFS].[Journal Voucher ID], Count([zData Navy FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 AFS].[Table Source]) AS [Table Source], First([zData Navy FY15 AFS].[dBranch]) AS [dBranch], First([zData Navy FY15 AFS].[DB_ID]) AS [DB_ID], First([zData Navy FY15 AFS].[Source]) AS [Source], First([zData Navy FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 AFS].[Reason]) AS [Reason], First([zData Navy FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 AFS].[Created Date]) AS [Created Date], First([zData Navy FY15 AFS].[Approval Date]) AS [Approval Date], First([zData Navy FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 AFS].[SUS ID]) AS [SUS ID], First([zData Navy FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 AFS].[Quarter]) AS [Quarter], First([zData Navy FY15 AFS].[FY]) AS [FY], First([zData Navy FY15 AFS].[Record Use]) AS [Record Use], First([zData Navy FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 AFS].[Reason])) AS [Length] FROM [zData Navy FY15 AFS] GROUP BY [zData Navy FY15 AFS].[Journal Voucher ID], [zData Navy FY15 AFS].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BBA GF].[Journal Voucher ID], Count([zData Navy FY15 BBA GF].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BBA GF].[Table Source]) AS [Table Source], First([zData Navy FY15 BBA GF].[dBranch]) AS [dBranch], First([zData Navy FY15 BBA GF].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BBA GF].[Source]) AS [Source], First([zData Navy FY15 BBA GF].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BBA GF].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BBA GF].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BBA GF].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BBA GF].[Reason]) AS [Reason], First([zData Navy FY15 BBA GF].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BBA GF].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BBA GF].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BBA GF].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BBA GF].[Created Date]) AS [Created Date], First([zData Navy FY15 BBA GF].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BBA GF].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BBA GF].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BBA GF].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BBA GF].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BBA GF].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BBA GF].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BBA GF].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BBA GF].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BBA GF].[Quarter]) AS [Quarter], First([zData Navy FY15 BBA GF].[FY]) AS [FY], First([zData Navy FY15 BBA GF].[Record Use]) AS [Record Use], First([zData Navy FY15 BBA GF].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BBA GF].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BBA GF].[Reason])) AS [Length] FROM [zData Navy FY15 BBA GF] GROUP BY [zData Navy FY15 BBA GF].[Journal Voucher ID], [zData Navy FY15 BBA GF].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BE].[Journal Voucher ID], Count([zData Navy FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BE].[Table Source]) AS [Table Source], First([zData Navy FY15 BE].[dBranch]) AS [dBranch], First([zData Navy FY15 BE].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BE].[Source]) AS [Source], First([zData Navy FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BE].[Reason]) AS [Reason], First([zData Navy FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BE].[Created Date]) AS [Created Date], First([zData Navy FY15 BE].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BE].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BE].[Quarter]) AS [Quarter], First([zData Navy FY15 BE].[FY]) AS [FY], First([zData Navy FY15 BE].[Record Use]) AS [Record Use], First([zData Navy FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BE].[Reason])) AS [Length] FROM [zData Navy FY15 BE] GROUP BY [zData Navy FY15 BE].[Journal Voucher ID], [zData Navy FY15 BE].[Record Use]
UNION ALL
SELECT [zData Navy FY15 WCF BBA].[Journal Voucher ID], Count([zData Navy FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData Navy FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData Navy FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData Navy FY15 WCF BBA].[Source]) AS [Source], First([zData Navy FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 WCF BBA].[Reason]) AS [Reason], First([zData Navy FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData Navy FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData Navy FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData Navy FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData Navy FY15 WCF BBA].[FY]) AS [FY], First([zData Navy FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData Navy FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 WCF BBA].[Reason])) AS [Length] FROM [zData Navy FY15 WCF BBA] GROUP BY [zData Navy FY15 WCF BBA].[Journal Voucher ID], [zData Navy FY15 WCF BBA].[Record Use]
UNION ALL
SELECT [zData USMC FY15 AFS].[Journal Voucher ID], Count([zData USMC FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 AFS].[Table Source]) AS [Table Source], First([zData USMC FY15 AFS].[dBranch]) AS [dBranch], First([zData USMC FY15 AFS].[DB_ID]) AS [DB_ID], First([zData USMC FY15 AFS].[Source]) AS [Source], First([zData USMC FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 AFS].[Reason]) AS [Reason], First([zData USMC FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 AFS].[Created Date]) AS [Created Date], First([zData USMC FY15 AFS].[Approval Date]) AS [Approval Date], First([zData USMC FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 AFS].[SUS ID]) AS [SUS ID], First([zData USMC FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 AFS].[Quarter]) AS [Quarter], First([zData USMC FY15 AFS].[FY]) AS [FY], First([zData USMC FY15 AFS].[Record Use]) AS [Record Use], First([zData USMC FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 AFS].[Reason])) AS [Length] FROM [zData USMC FY15 AFS] GROUP BY [zData USMC FY15 AFS].[Journal Voucher ID], [zData USMC FY15 AFS].[Record Use]
UNION ALL
SELECT [zData USMC FY15 BE].[Journal Voucher ID], Count([zData USMC FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 BE].[Table Source]) AS [Table Source], First([zData USMC FY15 BE].[dBranch]) AS [dBranch], First([zData USMC FY15 BE].[DB_ID]) AS [DB_ID], First([zData USMC FY15 BE].[Source]) AS [Source], First([zData USMC FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 BE].[Reason]) AS [Reason], First([zData USMC FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 BE].[Created Date]) AS [Created Date], First([zData USMC FY15 BE].[Approval Date]) AS [Approval Date], First([zData USMC FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 BE].[SUS ID]) AS [SUS ID], First([zData USMC FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 BE].[Quarter]) AS [Quarter], First([zData USMC FY15 BE].[FY]) AS [FY], First([zData USMC FY15 BE].[Record Use]) AS [Record Use], First([zData USMC FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 BE].[Reason])) AS [Length] FROM [zData USMC FY15 BE] GROUP BY [zData USMC FY15 BE].[Journal Voucher ID], [zData USMC FY15 BE].[Record Use]
UNION ALL
SELECT [zData USMC FY15 WCF BBA].[Journal Voucher ID], Count([zData USMC FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData USMC FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData USMC FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData USMC FY15 WCF BBA].[Source]) AS [Source], First([zData USMC FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 WCF BBA].[Reason]) AS [Reason], First([zData USMC FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData USMC FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData USMC FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData USMC FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData USMC FY15 WCF BBA].[FY]) AS [FY], First([zData USMC FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData USMC FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 WCF BBA].[Reason])) AS [Length] FROM [zData USMC FY15 WCF BBA] GROUP BY [zData USMC FY15 WCF BBA].[Journal Voucher ID], [zData USMC FY15 WCF BBA].[Record Use];

“原因”字段是“备注”字段。

SQL语句另存为查询：“数据导出”，然后保存为我们使用以下命令构建的表：

DoCmd.RunSQL "SELECT * INTO [DATA OUTPUT TABLE] FROM [Data Export];"

各个选择语句产生的结果带有适当的长度，并且[Length]字段填充了原始字符串的长度，但是UNION ALL查询返回的<= 255个字符字符串。像“数据导出”记录集一样，其默认设置为“短文本”。