locked
Form not updateable after migrating DB from Access to SQL Server RRS feed

  • Question

  • I have a select statement behind a sub-form in my access application with DB also in access backend. This select statement selects the lone column in a table which of alpha-numeric data type.

    Following is the script to create this table and data.

    CREATE TABLE tblTest(
        Test    varchar(6)    NOT NULL
    )

    ALTER TABLE [tblTest] ADD
     CONSTRAINT pktblTest PRIMARY KEY NONCLUSTERED (Test)
    GO

    INSERT [tblTest] (Test) VALUES ('10')
    INSERT [tblTest] (Test) VALUES ('100')
    INSERT [tblTest] (Test) VALUES ('10D')
    INSERT [tblTest] (Test) VALUES ('112')

    INSERT [tblTest] (Test) VALUES ('20')
    INSERT [tblTest] (Test) VALUES ('213')
    INSERT [tblTest] (Test) VALUES ('21B')

    INSERT [tblTest] (Test) VALUES ('32')
    INSERT [tblTest] (Test) VALUES ('381')
    INSERT [tblTest] (Test) VALUES ('38B')

    GO

    My access front-end uses VAL function to sort the out put so that the non-numeric values are ignored in the sort. My list displayed with the above data set will look as follows...

    10
    10D
    20
    21B
    32
    38B
    100
    112
    213
    381

    We have recently migrated our backend to SQL Server. Since then this query stopped working since VAL is not a recognized function in SQL. Since I can not use SQL specific function in Access query, I have written the following stored procedure to get the work done.

    CREATE PROCEDURE sp_GetTestList
    AS
    BEGIN

     -- Sorts the Test list appropriately.
     -- Without this sort the list would display 10, 100, 10D, 112, 20, 213, 21B, 32, 381, 38B
     -- This proc would return the above Test list as 10, 10D, 20, 21B, 32, 38B, 100, 112, 213, 381
     
     SELECT tblTest.Test
     FROM tblTest (NOLOCK)
     ORDER BY
       CAST (
        CASE SIGN (PATINDEX ('%[0-9]%', tblTest.Test))
         WHEN 0 THEN NULL
         ELSE
          CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
           WHEN 0 THEN tblTest.Test
           ELSE LEFT (tblTest.Test, PATINDEX ('%[^0-9]%', tblTest.Test) -1)
          END
        END
       AS Numeric),
       CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
        WHEN 0 THEN NULL
        ELSE RIGHT (tblTest.Test, LEN (tblTest.Test) - (PATINDEX ('%[^0-9]%', tblTest.Test)) + 1)
        END

    END

    GO


    I have linked this proc to be used as a pass through query in access. It works as desired for sorting. But now I lost the ability to add new rows. What should I do so that I can add new records to the list displayed? Access didn't have a problem with it. I have tried changing it to a view with TOP 100 PERCENT option and that did not work either. This is the only column in this table. It is unique and is the primary key for this table.

    Please help me in finding a resolution for this issue.

    Regards,
    Helios

    Thursday, December 1, 2011 4:21 PM

Answers

  • Hi Helios,

    Pass-through queries are Read-Only. Therefore when you use a pass-through query for the RecordSource of a Form the Form cannot be used to update or insert new records.

    You can add a "Save" button to use code to execute another Stored Procedure that will update or insert a record.  Another option is to use a linked View or Table for your Form's RecordSource.


    Patrick Wood, Access MVP
    Founder, Gaining Access Technologieshttp://gainingaccess.net/
    • Marked as answer by Bruce Song Tuesday, December 6, 2011 7:18 AM
    Thursday, December 1, 2011 5:06 PM

All replies

  • Hi Helios,

    Pass-through queries are Read-Only. Therefore when you use a pass-through query for the RecordSource of a Form the Form cannot be used to update or insert new records.

    You can add a "Save" button to use code to execute another Stored Procedure that will update or insert a record.  Another option is to use a linked View or Table for your Form's RecordSource.


    Patrick Wood, Access MVP
    Founder, Gaining Access Technologieshttp://gainingaccess.net/
    • Marked as answer by Bruce Song Tuesday, December 6, 2011 7:18 AM
    Thursday, December 1, 2011 5:06 PM
  • Helios, how about the problem on your side? If you still show any concern on the problem, just feel free to let us know.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 3:23 AM