如何通过MS Access在SQL Server中创建临时表 [英] How to create temp tables in SQL Server via MS Access

问题描述

我有一个到SQL Server的ODBC连接.我需要在Access中处理数据，但是需要太长时间.我的想法是将这些数据推送到SQL Server临时表并让SQL Server进行处理.我的Access数据库中有许多传递查询，但不知道如何从Access到SQL Server创建临时表.有没有办法使用查询或VBA代码来执行此操作?

I have an ODBC connection to SQL Server. I need to process data within Access, but it takes too long. My idea is to push this data to a SQL Server temp table and have SQL Server do the processing. I have many pass-through queries in my Access database but do not know how I can create a temp table from Access to SQL Server. Is there a way to do this using either a query or VBA code?

推荐答案

这是我用来调用DB2存储过程的VBA代码段.任何DDL语句都应使用相同的技术.为此，创建一个传递查询并将CREATE TABLE #tblname...语句作为其SQL文本.

Here is a snippet of VBA code I used to call a DB2 stored procedure. The same technique should work for any DDL statement. To do this, create a pass-through query and put your CREATE TABLE #tblname... statement as its SQL text.

重要提示:然后打开查询的属性表，并将返回记录"属性设置为否".

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qry_SP_CHANGE_COLUMN")
qdf.Connect = CurrentDb.TableDefs("SCHEMA_tblName").Connect
qdf.SQL = "call SCHEMA.SP_CHANGE_COLUMN(...)"
qdf.Execute dbFailOnError
qdf.Close
Set qdf = Nothing

注意，您可能不必更改SQL文本.如果表结构从未更改，则可以将其保留在查询def中.

Note, you probably won't have to change your SQL text. You can just leave that in the query def if the table structure never changes.

您面临的挑战是，针对临时表的任何操作都必须使用相同的连接.连接关闭的那一刻，您的临时表将消失，因为它是本地临时表，并且仅对该连接可见.如果您有权这样做，可以使用全局临时表"##"来避免这种情况.

The challenge for you is that you must use the same connection for any operations against the temp table. The minute the connection is closed, your temp table will vanish because it's a local temp table, and it's only visible to that one connection. You can avoid this by using '##', global temp tables, if you have rights to do that.

这篇关于如何通过MS Access在SQL Server中创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！