How to call an ALTER VIEW Table Based Function from MS Access on-demand

[Rx_]

Any ideas of how to call a Table Based Function from MSAccess on SQLServer when there is an ALTER?

In SQL Server - there is a table based function that must be run every time the base table data changes to update a couple of dozen views.
These views are connected by Linked Tables to MSAccess.
While a SP can be run on SQL Server from MSAccess
it appears that SQL Server no longer allows Access to call a remote ALTER.

These records in the views are tiny (under 50,000 rows).
The objective is to run the ALTER VIEW before pulling the data from these views. MSAccess has a linked table to each of the two dozen business views.

This should be of interest to Azure users as well.


<for each Org in>
select * from Capital.dbo.Organizations
where is_included = 1
ALTER VIEW [dbo].[VW_DBUploadBus]
-- select * from dbo.VW_DBUploadBus
AS
select * from dbo.tfn_DBUploadByOrg('Bus')
GO
..... (same alter view for each of couple dozen orgs e.g. 'Bus'

 

[Minty]

Are you saying you can't you call the SP to do the ALTER from Access ?

 

[Rx_]

Yes, access 2016 and SQL Server 2015

 

[Minty]

To be fair it's not something I've tried.
Are the results of these views able to be created in a dynamic SP itself, that might be a better route rather than altering the view everytime?

(Obviously there maybe much more to this that I'm picking up on)

 

[kevlray]

Just a thought. It is possibly a rights issue on the SQL server side? I know that they have been getting better about locking. I have never called a SP from Access, so not even sure how to do it. So it seems to me if you can even call the SP, after then it is all up to SQL server security.

 

[Rx_]

The MSAccess calls a SP that runs a significant amount of code and creates tables. The Tables are the Linked Tables in MS Access.
After the tables are refreshed on SQL Server, a MakeTable is run to get the data over to MSAccess quickly. From there the Access vba code uses Excel Object Model creates many custome"reports" in macro-free Excel.

Normally, a SQL View can be used. However, this location only has a tiny virtual machine running SQL that is so limited on resources... my local laptop running SQL is many times faster.
By updating a table on SQL, the resource is used once.
Then, the network is not that great. The ODBC timeout on linked tables was common when trying to use a recordset on a linked table. The MakeTable from a linked table has something that brings over the data much, much faster. This eliminated the ODBC timeouts and allowed the Excel reports to be created many times faster. Turns out, it was nice for troubleshooting too.

Here is the code to call a SP.

Public Sub SP_RefreshsQLserver1()
Dim qdef As DAO.QueryDef
' Runs stored procedure to update data for sort order and record inclusion
    Set qdef = CurrentDb.CreateQueryDef("")
    
    qdef.Connect = CurrentDb.TableDefs("[dbo_VW_WeeklySummaries_CqForecastProjectX]").Connect
    qdef.SQL = "EXEC SP_fill_weeklySummaries"
    qdef.ReturnsRecords = False
    qdef.Execute
    'Debug.Print Err.Description
End Sub