将 SQL Server 查询结果复制到 Access 2010 表中 [英] Copying SQL Server query results into an Access 2010 table

问题描述

每月，我需要从我们的 SQL Server 业务应用程序获取新"数据到包含我们所有管理报告"的 Access 2010 数据库中.Access 数据库有一个临时表"，用于包含报告所基于的原始数据.

Monthly, I need to get "new" data from our SQL Server business application into an Access 2010 database that contains all our "Management Reports". The Access database has a "staging table" that is to contain the raw data on which the reports are based.

我没有 Access 经验，但我建议我们:

I have no Access experience, but I suggested that we:

1. 在我们的 SQL Server 上编写一个查询(存储过程?)，返回所需的原始数据(...这点很简单.)
2. 在每个月底，从 Access 2010 中调用 SQL Server 存储过程(...单击按钮?)
3. 将存储过程的结果保存到 Access 中的临时表中.

但我发现它比我预期的要难.我想我可以在代码隐藏中使用 ADODB 得到一些丑陋的工作，一一循环记录集中的行，然后一一设置列值.但一定有更好的方法:)

But I'm finding it harder than I expected. I think I can get something ugly working using ADODB in a code-behind, looping through rows in a recordset one by one, and then setting column values one by one. But there must be a better way :)

• 我应该如何从 Access 2010 获取 SQL Server 数据?(ADODB?DAO?QueryDesigner?其他?)

• How should I go about getting SQL Server data from Access 2010? (ADODB? DAO? QueryDesigner? other?)

是否有我可以利用的将记录集插入表中"(或类似)机制?

Is there a "Insert Recordset Into Table" (or similar) mechanism that I can leverage?

推荐答案

将相关的 sql server 表或视图链接到 MS Access.使用 MS Access 语法对链接表运行查询并更新临时表.

Link the relevant sql server table or view to MS Access. Run a query against the linked table using MS Access syntax and update the staging table.

还可以在查询中使用 SQL Server 的连接字符串更新 MS Access 表.

It is also possible to update an MS Access table using a connection string for SQL Server in-line in your query.

SELECT *
INTO   newtable
FROM   [odbc;filedsn=Z:\DSN\test.dsn].table1

从 SQL Server 端工作，您可以使用 MS Access 作为链接服务器或从那里运行查询和更新.

Working from the SQL Server end, you can use MS Access as a linked server or run a query and update from there.

INSERT INTO
OPENDATASOURCE(
   'Microsoft.ACE.OLEDB.12.0', 'Data Source=z:\docs\test.accdb')...[table1] 
   ( atext )
SELECT atext FROM table1 WHERE id=2

这篇关于将 SQL Server 查询结果复制到 Access 2010 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！