none
Conersion ADP to ACCDB format RRS feed

  • Question

  • One legacy application is in adp format with linking to SQL Server 2012. To support MS-Access 2013, it is required to convert legacy application to .accdb format. But while linking to SQL server, it creates links for SQL table only (even views are linked as table only). Rest of SQL server object like function, stored procedure are not linked as it linked in legacy MS-Access application.

    Issue is that, after converting project to accdb format, some linked views are not showing data on MS-access side. I found these views are using some complex function (sql server object). Since function is not linked (or available on MS-Access side) therefore MS-Access is showing zero record. Same view return multiple records on SQL server side. There is no issue with tables.

    So Is there any way to create link for functions, stored procedures and views like legacy application (ADP format)? Because re-writing View, function and stored procedure as query inside MS- Access will be time consuming. Also at some places we are directly assigning view name to property of access-form (ex  Record source)

    Monday, October 5, 2015 2:25 PM

Answers

  • Something else is causing your problems. SQL functions and stored procedures stay in the back end. There is no need to "link" to them. SQL can see them just fine. But you might have to call them using pass-through queries as Access ACE can't use them like they were native.

    If you views are not showing correctly run sp_refreshview 

    on each view in the back end. Then force Access to relink to the views.

    You can refresh all views using this:

    /*
    Procedure to refresh all views' metadata.
    Necessary if changes are made to underlying objects.
    */
    USE msdb
    GO
    
    SET NOCOUNT ON
    
    DECLARE @myview VARCHAR(255)
    
    DECLARE myviews CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE type = 'V'
        AND name not like 'sys%'
    
    
    OPEN myviews
    
    FETCH NEXT
        FROM myviews
        INTO @myview
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_refreshview @viewname = @myview
    
        FETCH NEXT
        FROM myviews
        INTO @myview
    END
    
    CLOSE myviews
    DEALLOCATE myviews
    print 'All Done'
    



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, October 5, 2015 2:57 PM

All replies

  • Something else is causing your problems. SQL functions and stored procedures stay in the back end. There is no need to "link" to them. SQL can see them just fine. But you might have to call them using pass-through queries as Access ACE can't use them like they were native.

    If you views are not showing correctly run sp_refreshview 

    on each view in the back end. Then force Access to relink to the views.

    You can refresh all views using this:

    /*
    Procedure to refresh all views' metadata.
    Necessary if changes are made to underlying objects.
    */
    USE msdb
    GO
    
    SET NOCOUNT ON
    
    DECLARE @myview VARCHAR(255)
    
    DECLARE myviews CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE type = 'V'
        AND name not like 'sys%'
    
    
    OPEN myviews
    
    FETCH NEXT
        FROM myviews
        INTO @myview
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_refreshview @viewname = @myview
    
        FETCH NEXT
        FROM myviews
        INTO @myview
    END
    
    CLOSE myviews
    DEALLOCATE myviews
    print 'All Done'
    



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, October 5, 2015 2:57 PM
  • Thanks for response. But I created below query inside accdb project (converted application).  In this query  ProjektID() and  VarianteID() are sql functions. While executing this query it is giving error that ProjectId and VarianteId are not defined

    Query

    SELECT * FROM dbo_tAdrAdress WHERE fiProjekt = ProjektID()   AND fiVariante = VarianteID();

    As ADP format of MS-Access allows to create link to SQL function and stored procedure. But same thing is not possible in case of accdb format.

    Wednesday, October 7, 2015 1:58 PM
  • As I said, you have to use a pass-through query if you want to use SQL functions/SPs.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, October 7, 2015 3:07 PM
  • Oh got it. Thank you Mosca.
    Thursday, October 8, 2015 9:34 AM
  • Oh got it. Thank you Mosca.
    You're welcome!

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Thursday, October 8, 2015 1:38 PM