Ms-access – MS Access replace entire table contents

See if this work.

 CREATE FUNCTION [dbo].[FN_CommaSeparator] (@array VARCHAR(max))

> RETURNS @Table TABLE (value VARCHAR(max))

AS
BEGIN
    DECLARE @separator_position INT
        ,@array_value VARCHAR(max)

    SET @array = @array + ','

    WHILE PATINDEX('%,%', @array) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @array)

        SELECT @array_value = LEFT(@array, @separator_position - 1)

        INSERT @Table
        VALUES (@array_value)

        SELECT @array = STUFF(@array, 1, @separator_position, '')
    END

    RETURN
END

CREATE TABLE #Temp (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

CREATE TABLE #Temp2 (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

INSERT INTO dbo.#Temp (
    Id
    ,Author
    )
VALUES (
    'S1'
    ,'ABC,BCD'
    )
    ,(
    'S2'
    ,'CDE,DEF'
    )
    ,(
    'S3'
    ,'EFG,FGH'
    )
    ,(
    'S4'
    ,'GHI,HIJ'
    )

DECLARE @Id VARCHAR(50)
    ,@Author VARCHAR(50)

DECLARE ABC CURSOR
FOR
SELECT Id
    ,Author
FROM dbo.#Temp t

OPEN ABC

FETCH NEXT
FROM ABC
INTO @Id
    ,@Author

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.#Temp2 (
        Id
        ,Author
        )
    SELECT @Id
        ,Value
    FROM dbo.FN_CommaSeparator(@Author)

    FETCH NEXT
    FROM ABC
    INTO @Id
        ,@Author
END

DEALLOCATE ABC

SELECT *
FROM dbo.#Temp2 t

DROP TABLE dbo.#Temp

DROP TABLE dbo.#Temp2

You are trying to execute SQL SERVER sql syntax in msaccess. Need to remove the nolock hints. The column and table alias need to b prefixed with AS. And more. Executing your query as a pass-through is probably a better option anyway.