Using Access as FE and external database BE - queries performance

J

jaryszek

Registered User.
Local time
Today, 06:59
Joined
Aug 25, 2016
Messages
756
Hi Guys,

where do you write queries when you are working with BE like SQL server ?

Are you using pass through queries ? And select only data?
Or you are referring to functions from FE in BE but all aggregations you are doing in BE?

Queries in Access are temporary tables which are taking space.
i mean i can have all tables in BE but writing queries in FE will take space...
Maybe all queries and views should be in BE and in FE i should only show snapshots from BE?
What are your approaches?

Best Wishes,
Jacek
 
Actually, on this fine point, the space taken by a query is just a list of pointers used for data retrieval, nothing else. A query is NOT a temporary table.

Having said that, there is nothing wrong with the idea of having passthru queries and there is nothing wrong with having stored views on the BE server. Either way works. What is far more important is to assure that you have indexes on the tables you want to query.

I can't tell you much about SQL Server - in my site, we used ORACLE as our BE and had to use SQLnet or ODBC linkage to get to the data. Didn't use SQL server that much. At least, I didn't. But others here will tell you lots more. And I have seen articles on making an SQL Server query more efficient.

Use the SEARCH option of this forum to look up "Query speed" and "Query efficiency" as sources for many articles, some quite recent, on the topic. They will discuss things in greater detail than I could.
 
My experience is that pass through queries will be generally faster - for small data sets, insignificant but for larger ones, very significant.
 
thank you Guys,

what is alternative for SQL pass-through query?
With pass-through query you can send command directly to BE, but what if you are creating forms. Still will you use pass-through query?

How to change in real time data in tables via for example input forms. Using pass-through?

Best Wishes,
Jacek
 
jaryszek said:
what is alternative for SQL pass-through query?
With pass-through query you can send command directly to BE, but what if you are creating forms. Still will you use pass-through query?
Click to expand...

Here is a link to Steve Bishop's channel. In this particular video, he connects a form directly to the SQL Server. I myself have been wanting to try it but have not made the time.

If you give it a go, come back here and let us know!

https://youtu.be/0Qi_EGitNhk
 
I generally start with forms bound to linked tables and Access queries for reports, etc. I switch to pass through queries, views, etc when performance is an issue. Access is pretty good about passing SQL back to the server and getting back just the necessary records, so a good deal of the time an Access query can be just as fast. It's when Access can't pass the work back to the server that performance suffers and we have to find alternate methods.
 
The attached example uses a similar idea to that in Steve Bishop's video but for an Access BE.
Full explanation given in the Word doc included in the zip file

The FE contains 2 forms, 1 report but no linked tables.
The record source for each form & the report are created when the object is opened & destroyed when it is closed.
So there are no connection strings visible anywhere

The same idea can be used with SQL Server tables.
This obviously is another way of adding security if needed.
I doubt performance is noticeably different to using standard linked tables
If using a passthrough query, it should be faster
 

Attachments

To amplify/clarify Paul's comment,...

If you create a query that CAN be passed back to the BE, that is what will happen. BUT if you have a query that contains a function defined in the FE, the BE cannot possibly execute it, so the whole domain has to be brought back to the FE for processing. Moral of the story? Look up all of the functions available on your BE server and use THEM in the queries intended as passthru. If you cannot limit yourself to such functions, expect any SQL to be slow when it has an FE-resident function to evaluation in the query.
 
Thank you!

It's when Access can't pass the work back to the server that performance suffers and we have to find alternate methods.
Click to expand...

Ok, so when i have a lot of queries with Access specific functions like IIF the problem can be to pass this query to BE yes? What methods are you using to do this?

Access query design editor is so intuitive and simple and creating queries is easy.
In other words transform Access Queries to MS SQL can be the issue?

---

Colin,

how to unhide deep hidden table? :) can you help with this, very interesting approach generally!

Best Wishes,
Jacek
 
Hi Jacek
The purpose of 'deep hidden tables' is for security.
On principle I never explain how to unhide them or the security would become worthless.
 
jaryszek said:
Ok, so when i have a lot of queries with Access specific functions like IIF the problem can be to pass this query to BE yes? What methods are you using to do this?
Click to expand...

There are many tools available including stored procedures, views, table valued functions, CTE's, etc. I can't say I have a rule as to when I'll use each, perhaps somebody else does. I've used all of those except CTE's, and for no better reason than I learned other tools first so I'm more comfortable with them.

There are some rules that drive which I'll choose. A view can't be parameterized, a stored procedure can't be called in the FROM clause of a query, etc.

I won't disagree that the Access query design grid is simple to use, but as you work with SQL more it becomes almost easier to read SQL than the design grid.

FYI, the IIf() function is valid in later versions of SQL Server (starting with 2012).
 
Thank you Paul,

stored procedures, views, table valued functions are called with pass through queries or vba yes ?

I won't disagree that the Access query design grid is simple to use, but as you work with SQL more it becomes almost easier to read SQL than the design grid.
Click to expand...

hmm so how can i move Access sqls to SQL server BE? Did you try somethinf like that?

Best,
Jacek
 
A view can be linked to an Access front end just like a table, or used in the other methods. The others are probably predominately used from those methods. A table valued function could be used within a stored procedure or anyplace else you might use a table.

I am definitely not an authority, I'm somebody who's beat their head against problems until they found something that worked. It may not always have been the best solution. ;) The Steve Bishop videos look like they're pretty good.
 
thank you pbaldy!

Awesome info, i will review this faq.

Best Wishes,
Jacek
 
No problemo! The info was worth what you paid for it. :p
 
I hadn't read Leigh's article for several years and it provided useful information to refresh my memory about certain aspects in SS.

Some of the info is out of date e.g. The Express edition of SS now supports up to 10GB (previously 4GB) but it remains an excellent resource for intermittent users as well as beginners

Thanks for the link Paul.
 
Last edited:
I should have reread it to make sure it was still relevant, but I remembered it being a big help. It is older though. Like me.
 
You must log in or register to reply here.

Similar threads

L
Solved Lock file errors on FE. Multiple access tasks in task manager.
Replies
2
Views
159
likecricketilovesit
L
C
Database slow on terminal server running Access Runtime
Replies
13
Views
605
Clem Fandango
C
E
Emmanuel Katto Dubai : How can I improve the performance of a slow-running SQL query in my large database?
Replies
2
Views
133
The_Doc_Man
The_Doc_Man
E
Emmanuel Katto from Dubai : Database Performance Issues on Terminal Server Running Access Runtime
Replies
3
Views
178
Cotswold
Cotswold
nimeshaanethmini
I Need Advice on Optimizing a Large Database Query
Replies
3
Views
215
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom