Back End Query concept (1 Viewer)

sxschech

Registered User.
Local time
Today, 08:33
Joined
Mar 2, 2010
Messages
791
Regarding this post, got me thinking.

Re: How to query a backEnd database from a frontend database with a Sql script

http://www.access-programmers.co.uk/forums/showthread.php?t=287797

Can this technique be used to create an equivalent of a "linked table" for queries. I have FE/BE and there are some queries that I would like to either be standardized or customized and protected from FE version updates. If these UD/UAQ (User Defined/User Altered Queries) were stored in a separate file, then I wouldn't have to worry about them being wiped out or replaced with a more generic version.
If I go this way, is there a performance issue per CJ London
benefit is the back end is open persistently so lower time overhead on getting the records, whereas your method isn't, although fine for occasional access to other data.
or some other unintended issue that may arise from this novel approach?
 

Simon_MT

Registered User.
Local time
Today, 15:33
Joined
Feb 26, 2007
Messages
2,177
Persistency is pretty standard for FE/BE deployments. Filtering is even quicker.

Simon
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:33
Joined
Feb 19, 2013
Messages
16,555
just to point out other benefits of my approach:

you can put your queries and run them in the back end using openrecordset (tho I guess you can do the same with the other basis) - much like a view in sql server
and you can access the querydefs if you want to modify them in some way (change a criteria for example)

what you can't do with my approach is have a query to combine data from multiple databases (unless they are linked in the back end - which you can do via vba if you wanted)

In all cases if this is to stop users messing with tables or queries directly, an educated user can still find the location and password of a backend - even with everything buried in code and compiled to a .accde.
 

Simon_MT

Registered User.
Local time
Today, 15:33
Joined
Feb 26, 2007
Messages
2,177
With SQL Server procedures, similar to a Function, are used to hide the data, as the biggest fear is a SQL Injection. The SQL DBA sole responsibility seems to be performance tuning. Data is valuable but it needs to be reported and even more importantly being able to test data integrity.

Unlike Views in SQL server Access SQL queries handle internal Expressions more efficiently. Iuse the SQL Views are building blocks.

SQL Server creates execution plans for SQL statements although I think Access can also optimise SQL Statements.

We should let the data tell its story.

Simon
 

Users who are viewing this thread

Top Bottom