如何通过传递查询将数据从Access表附加到SQL Server表? [英] How to append data from an Access table to a SQL server table via a pass-through query?

问题描述

似乎只能使用传递查询从SQL Server表中检索数据并进入MS Access.但是反过来呢?从Access表到SQL Server表.

It seems like it's only possible to use a pass-through query to retrieve data from your SQL Server tables and into MS Access. But how about the other way? From an Access table to a SQL server table.

当我需要高性能时，我在MS Access中有哪些选择?
(将附加查询附加到链接表的正常方法只是太慢)

What are my options from within MS Access when I need high performance?
(The normal approach of having an append query that appends to a linked table is simply too slow)

在传递查询中，我无法引用MS Access表或查询，因此我的INSERT INTO语句无法工作.是否可以通过VBA解决此问题?

In an pass-through query I cannot reference MS Access tables or queries, and therefore my INSERT INTO statement cannot work. Is there a work around via VBA?

推荐答案

您可以在传递查询中使用OPENROWSET.

You can use OPENROWSET in a passthrough query.

SELECT id,
       atext
INTO   anewtable
FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                  'z:\docs\test.accdb'; 'admin';'',table1); 

您可能需要部分或全部这些选项:

You may need some or all of these options:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
     N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
     N'DynamicParameters', 1
GO

我怀疑会不会更快.

这篇关于如何通过传递查询将数据从Access表附加到SQL Server表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！