使用Excel的Microsoft Query连接Access查询遇多库打开错误及数据缺失求助

• Access数据库关联11个外部Excel表格，结合本地数据执行多组查询/联合查询，需将最终查询结果动态链接至新Excel表格（支持点击更新同步数据变更）
• 使用「数据-获取数据-其他来源-Microsoft Query」时触发**“数据库打开过多”**错误，已排查：
  • 所有数据存储在C盘桌面，且路径已加入Excel信任中心
  • 通过Available Connections插件确认剩余212个可用连接，排除连接数不足问题
• 使用「数据-获取数据-数据库-Access数据库」方法时，Access查询结果显示71行，但Excel仅导入62行，莫名缺失9行非复杂查询相关数据

一、修复现有方法的问题 #

1. 解决“数据库打开过多”错误（Microsoft Query法） #

• 关闭Access中所有未使用的查询、表对象：用Microsoft Query前，只保留需要的查询处于打开状态，避免后台隐式连接占用资源
• 优化Access的外部Excel链接：
  • 把11个Excel表格转成Excel表格格式（Ctrl+T），再重新链接到Access，降低连接开销
  • 在Access中给外部链接表设置**“仅加载必要字段”**，别加载冗余数据浪费连接资源
• 修改Excel的ODBC连接属性：
  1. 打开「数据-现有连接」，找到对应的Microsoft Query连接
  2. 点击「属性-定义」，在连接字符串末尾加 ;MaxBufferSize=1024000;PageTimeout=5，调整缓存和超时参数缓解连接压力

2. 解决数据缺失问题（Access数据库导入法） #

• 检查Access查询的返回设置：打开目标查询的「设计视图-属性」，确认「唯一值」「唯一记录」没被勾选（勾选会过滤重复行，可能导致数据缺失）
• 排查数据类型兼容性：
  • 核对Access查询里各字段的数据类型，确保和Excel列类型匹配（比如Access的“备注”字段可能被Excel截断，转成“文本”字段试试）
  • 导入时选「编辑查询」，确认所有字段都被选中，没被意外排除
• 刷新Access缓存：在Access里按F5执行「刷新全部」，再重新从Excel导入，避免读取旧缓存数据

二、动态链接替代方法 #

1. Access宏导出+Excel刷新链接 #

• 在Access里创建宏，把目标查询导出到指定Excel的工作表（覆盖旧数据），宏代码用VBA：

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "你的查询名称", "C:\桌面\目标Excel文件.xlsx", True, "目标工作表名称"
  

• 在Excel里通过「数据-编辑链接」把该工作表设为可刷新，点「全部刷新」就能同步Access数据
• 进阶：在Excel加个按钮，关联VBA调用Access宏，实现一键刷新

2. Power Query动态加载Access查询 #

• 操作步骤：
  1. 打开Excel，选「数据-获取数据-数据库-从Access数据库」
  2. 选你的Access文件，在导航器里直接选目标查询（别选表），点「加载-仅创建连接」
  3. 右键连接，选「加载到-表」设置加载位置
  4. 更新时点「数据-全部刷新」就行
• 优势：Power Query自动处理数据类型兼容问题，支持自定义加载规则，不会轻易丢数据，而且连接资源占用比Microsoft Query低

3. ODBC直接连接Access查询 #

• 操作步骤：
  1. 打开「控制面板-ODBC数据源(64位)」，添加「Microsoft Access Driver (*.mdb, *.accdb)」数据源，指向你的Access文件
  2. Excel里选「数据-获取数据-从ODBC」，选新建的数据源，导航里选目标查询
  3. 加载后点「数据-全部刷新」就能更新
• 优势：ODBC连接稳定性更高，能避免“数据库打开过多”这类资源占用问题

内容的提问来源于stack exchange，提问作者FoolzRailer