Solved Link to a query in Access BE?

[tvanstiphout]

If SQL Server is the back-end (BE), we can link to tables and views. Views are like queries.
However, if Access is the BE, we can only link to tables, not queries.
My form is listing BE objects and I have a "View Data" button. When clicked, I link to that table, and use DoCmd.OpenTable to view the data.
Alas, that does not work for queries.

It would be nice if OpenTable (and OpenQuery) was part of the Database object.

Copying the query data to the FE is not a winning strategy; some queries are just too large.
I thought of a local query with a FROM ... IN [path-to-BE] clause but it appears MSFT just broke that for security reasons.

Can anyone think of a slimy hack to work around this issue?

 

[GPGeorge]

Upsize to SQL Server?

 

[tvanstiphout]

Upsize to SQL Server?

No dice. The user of the app can select which BE to connect to and scan (for purposes of Anonymizing the data), and I'm allowing them to select either Access or SQL Server BEs.

 

[GPGeorge]

I figured that, but I couldn't resist the temptation. I apologize.

 

[tvanstiphout]

I figured that, but I couldn't resist the temptation. I apologize.

I thought you were my friend

 

[isladogs]

@tvanstiphout
No slimy hack required!
You can still link to queries in the BE
The new security restrictions ONLY affect Access databases where the FE isn't trusted

You can still do either of these approaches to link to a BE query as long as the FE is trusted
SELECT * FROM [Full path to BE database].QueryName_in_BE;
Or
SELECT * FROM QueryName_in_BE IN 'Full path to BE database';

For example both of these work

If not trusted, you will now get this error trying to run a query from the BE (unless you have set the registry key AllowQueryRemoteTables = 1)

See https://www.access-programmers.co.u...tion-not-supported.335005/page-2#post-1984553

 

[tvanstiphout]

Hi Colin,
That may work, especially if I can trap that error and tell user to move the BE to a TL. I will try it tomorrow (your Today ).

 

[CJ_London]

Not sure if it fits the bill, but you can use sql

SELECT * FROM qryView IN '' [ms access;pwd=;Database=pathtoBE.accdb]

or simply

SELECT * FROM qryView IN 'pathtoBE.accdb'

or in VBA you could try this- but requires a form to display the data

Dim ws As DAO.Workspace
Dim BE As DAO.Database
Dim rs As DAO.Recordset

    Set ws = DBEngine(0)
    Set BE = ws.OpenDatabase("pathtoBE.accdb")
    Set rs = BE.OpenRecordset("qryView")
    'apply to a form recordset

Edit: looks like Colin has beaten me to it