MS Access 2010无法导入SQL查询

Question

我在SQL中有一个dbo表格，其中有一列需要根据分隔符（,）拆分为多列。实现此操作的代码在本问题的末尾。该代码作为查询完美运行，但我想在MS Access 2010中导入最终的表格（即拆分后）。这时出了问题，因为我找不到ODBC或查询文件中的表格。另外，由于“Declare”函数，我不能将此代码放入视图功能中。该代码来自以下地址（它还显示了我想要对我的代码进行的操作）： https://raresql.com/2015/08/22/sql-server-how-to-split-one-column-into-multiple-columns/ 你能帮我解决这个问题吗？

下面是用于拆分1列为多列的代码：

DECLARE @delimiter VARCHAR(50)
SET @delimiter=', '

;WITH CTE AS
( 
    SELECT  [Tour number], 
            [TISLOT Time slot begin],
            [TISLOT Delivery day],
            [Gate],
            CAST('<M>' + REPLACE([Gate], @delimiter , '</M><M>') + '</M>' AS XML) AS [Gate XML]
    FROM dbo.TISLOT
)

SELECT  [Tour number], 
        [TISLOT Time slot begin],
        [TISLOT Delivery day],
        [Gate],
        [Gate XML].value('/M[1]', 'varchar(50)') As [Gate1],
        [Gate XML].value('/M[2]', 'varchar(50)') As [Gate2],
        [Gate XML].value('/M[3]', 'varchar(50)') As [Gate3],
        [Gate XML].value('/M[4]', 'varchar(50)') As [Gate4],
        [Gate XML].value('/M[5]', 'varchar(50)') As [Gate5],
        [Gate XML].value('/M[6]', 'varchar(50)') As [Gate6],
        [Gate XML].value('/M[7]', 'varchar(50)') As [Gate7],
        [Gate XML].value('/M[8]', 'varchar(50)') As [Gate8],
        [Gate XML].value('/M[9]', 'varchar(50)') As [Gate9],
        [Gate XML].value('/M[10]', 'varchar(50)') As [Gate10]
FROM CTE 
GO

感谢您的预先帮助。

- TruckTruck

---

为什么不排除声明和使用 Replace([Gate], ', ', '') 呢？你没有变量，可以创建一个视图。 - PSVSupporter

Answer 1

考虑到两个特殊的查询对象（均可在Ribbon中使用）在MS Access中：

1. Pass-Through查询允许您保留连接后端的SQL Server语法，但从MS Access内部运行；这将需要在创建时指定ODBC / OLEDB设置。
2. 使用Make-Table操作查询从上述Pass-Through查询生成本地Access表。

Pass-Through查询

（将其保存为存储的Pass-Through查询对象，将CTE略微调整为派生表，但CTE也可以工作）

SELECT  [Tour number], 
        [TISLOT Time slot begin],
        [TISLOT Delivery day],
        [Gate],
        [Gate XML].value('/M[1]', 'varchar(50)') As [Gate1],
        [Gate XML].value('/M[2]', 'varchar(50)') As [Gate2],
        [Gate XML].value('/M[3]', 'varchar(50)') As [Gate3],
        [Gate XML].value('/M[4]', 'varchar(50)') As [Gate4],
        [Gate XML].value('/M[5]', 'varchar(50)') As [Gate5],
        [Gate XML].value('/M[6]', 'varchar(50)') As [Gate6],
        [Gate XML].value('/M[7]', 'varchar(50)') As [Gate7],
        [Gate XML].value('/M[8]', 'varchar(50)') As [Gate8],
        [Gate XML].value('/M[9]', 'varchar(50)') As [Gate9],
        [Gate XML].value('/M[10]', 'varchar(50)') As [Gate10]
FROM 
   ( 
    SELECT  [Tour number], 
            [TISLOT Time slot begin],
            [TISLOT Delivery day],
            [Gate],
            CAST('<M>' + REPLACE([Gate], ',' , '</M><M>') + '</M>' AS XML) AS [Gate XML]
    FROM dbo.TISLOT 
   ) AS dT

制表查询

(操作查询可运行一次或保存为存储的查询对象以供定期使用)

SELECT * 
INTO [NewMSAccessLocalTable]
FROM [SQLServerPassThruQuery]

- Parfait

Answer 2

你可以将代码放入存储过程中，然后从Access调用它，并借助记录集将其放入表中：

Dim db As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rstCurr As DAO.Recordset
Dim dbsCurr As Database

db.Open "Provider=SQLNCLI11;Server=SERVER\INSTANCE;Database=MyDataBase;Trusted_Connection=yes;"
db.CommandTimeout = 180
db.CursorLocation = adUseClient

Set rs = db.Execute("EXEC dbo.StoredProc")

Set dbsCurr = Access.CurrentDb
Set rstCurr = dbsCurr.OpenRecordset("AccessTable", dbOpenDynaset)

Do Until rs.EOF

    rstCurr.AddNew
    rstCurr.Fields(0).value = rs.Fields(0).value
    rstCurr.Fields(1).value = rs.Fields(1).value
    ...

    rstCurr.Update
    rs.MoveNext
Loop

Set rs = Nothing
db.Close: Set db = Nothing

- gofr1

---

这个运作得非常完美。然而，我使用了下面的代码来读取Access：在Access中创建一个新查询，请不要添加任何表格。单击查询~ SQL特定 ~ 通过。在SQL窗口中，键入您的语句，例如EXEC usp_MyProcedure '6/6/2008'然后单击查看~属性，在ODBC连接字符串下单击构建按钮(...)并选择指向您的SQL数据库的DSN。 谢谢大家 - TruckTruck