SQL query cannot be imported into MS Access 2010

Question

I have a dbo table in SQL with a column that I need to split into multiple columns based on delimiter (,). The code to execute this is at the end of this question. The code works fine as a query, but I would like to import the final table (so after the partition) in MS Access 2010. This was wrong, because I can not find the table with ODBC or the query file. Also, due to the declare function, I cannot put this code in the view function. The code is (it also shows what I want to do with my code): https://raresql.com/2015/08/22/sql-server-how-to-split-one-column-into-multiple-columns/

Can you help me?

To split 1 column into multiple columns, the following code is used:

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 

Thank you in advance

Answer 1

You can put your code in a stored procedure. Then call it from Access and put it into the table using a set of records:

 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 

Answer 2

Consider two special request objects (both available on tape) in MS Access:

• A pass-through request that allows you to save the SQL Server syntax of the connected BackEnd, but executed from within MS Access; To do this, you need to specify the ODBC / OLEDB settings at creation.
• Request a Make-Table action to create a local access table from above through a request.

Pass Request

(saving as a stored, end-to-end query object, slightly adjusted CTE to the view, but CTE cannot work for no reason)

 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 

Make-Table Query

(an action request can be run once or saved as a stored request object for regular use)

 SELECT * INTO [NewMSAccessLocalTable] FROM [SQLServerPassThruQuery]