Theo Fitchner
asked on
MS Access: Is there an equivalent of Pass-through Queries that allows adding/editing records?
Hi everyone. I'm designing a database app by using Microsoft Access as a front end (FE) to a MS SQL Server backend (BE). I want to optimize performance as much as possible. Currently, I'm using linked tables. However, for some of my queries, I use pass-through queries and it returns its results instantly. As I have observed, I can still use pass-though queries without linking the tables in the BE. The problem is that the results of pass-through queries are read-only.
Presently, for adding/editing records, I build those queries by basing them on the relevant linked tables. Sometime, I add records by building the query as an Append Query (INSERT INTO). Is there any equivalent of a pass-through query that allows me to add/edit records? Please note that I will like to accomplish this using either the Query Design Grid (QDG) or by writing pure SQL in the QDG.
Thanks.
Presently, for adding/editing records, I build those queries by basing them on the relevant linked tables. Sometime, I add records by building the query as an Append Query (INSERT INTO). Is there any equivalent of a pass-through query that allows me to add/edit records? Please note that I will like to accomplish this using either the Query Design Grid (QDG) or by writing pure SQL in the QDG.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With linked tables, the Timestamp (RowVersion) column allows Access to identify write conflicts, but when using the technique mentioned above, you don't have that advantage.
As a side comment to what Dale said, most systems use some means of doing record locking on their own, along the lines of:
https://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have found a more efficient way to add records by using a view. I limit the data (results) returned by the view to just one by using the TOP 1 statement in the underlying SQL of the view. I have tested it and it works. However, I fear it might delay just a little bit when an image is included in that one record that is returned. For example, adding a new employee to an Employees table. Thanks to @Ste5an for the idea of limiting the number of records returned.
ASKER
@PatHartman
For editing a record, how do you specify your criteria at runtime?
I understand that Access always pulls down all the data in a table or tables before it can apply any criteria that is specified in the query. I'm trying to avoid this. When I'm searching for a record for editing purposes, I usually specify criteria at runtime in either of two ways:
1. Like "" & [Enter Employee ID:\]
or
2. [Forms]![EditEmployeesForm
Please note that the two methods above work very well if I create a normal SELECT query in Access with a pass-though query as its record source. But the resultset is read-only because the query is based on a pass-through query, and thus, uneditable.
For editing a record, how do you specify your criteria at runtime?
I understand that Access always pulls down all the data in a table or tables before it can apply any criteria that is specified in the query. I'm trying to avoid this. When I'm searching for a record for editing purposes, I usually specify criteria at runtime in either of two ways:
1. Like "" & [Enter Employee ID:\]
or
2. [Forms]![EditEmployeesForm
Please note that the two methods above work very well if I create a normal SELECT query in Access with a pass-though query as its record source. But the resultset is read-only because the query is based on a pass-through query, and thus, uneditable.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@PatHartman
I just realised that we've discussed this issue before via private messages.
I just realised that we've discussed this issue before via private messages.
ASKER
I said earlier that "I understand that Access always pulls down all the data in a table or tables before it can apply any criteria that is specified in the query".
Opinions seem to be divided on this issue. @PatHartman maintains that this is not the case if criteria is specified in the query. Specifying criteria is what I normally do. I'm just wondering if there is a different way.
Opinions seem to be divided on this issue. @PatHartman maintains that this is not the case if criteria is specified in the query. Specifying criteria is what I normally do. I'm just wondering if there is a different way.
ASKER
@Jim Dettman
I just tried this: Select <fields> FROM <my table> WHERE 1=0
It works. Thanks.
I just tried this: Select <fields> FROM <my table> WHERE 1=0
It works. Thanks.
I understand that Access always pulls down all the data in a table or tables before it can apply any criteria that is specified in the query.
That indeed is not the case. With a SQL data store, Access will always try and hand off the SQL statement to SQL Server when it can. When it can't is when:
1. You use VBA expressions in the query
2. You join to a local table.
3. You use JET/ACE specific SQL, like GROUP BY.
If it can't hand it off, then it will process it client side and may start pulling data across. But based on the query costing plan, it may use available indexes to restrict the result set without looking at the actual data with a table scan.
So in the end, the only data records it may pull are those that you want.
Jim.
ASKER
@Jim Dettamn
Well Number 2 on your list (Joining to local tables) is pretty much impossible to avoid in most queries.
About Number 1: VBA expressions in the query
I don't know of any other way to specify criteria in an easy to use format. The criteria example below is a VBA expression.
[Forms]![EditEmployeesForm
Do you know of another way that will be easy for users of such 'apps'?
Well Number 2 on your list (Joining to local tables) is pretty much impossible to avoid in most queries.
About Number 1: VBA expressions in the query
I don't know of any other way to specify criteria in an easy to use format. The criteria example below is a VBA expression.
[Forms]![EditEmployeesForm
Do you know of another way that will be easy for users of such 'apps'?
Well Number 2 on your list (Joining to local tables) is pretty much impossible to avoid in most queries.Why, if you have a SQL Server BE would you have data tables in the FE or a Jet/ACE BE? The only local tables I have are the switchboard, the FE update log, and some tables that are used to manage reports. None of these tables contain any data and so would never be joined to a SQL Server table.
I don't generally use the Where somefield =0 trick because it means that you have to replace the RecordSource. If I have only a couple of criteria options, I use combo or text boxes on the form itself. If I have a fancy search procedure, then I use a separate search form and I do build the SQL on the fly. So, most forms have queries like:
Select ...
From ...
Where FieldA = Forms!myform!txtSearchFiel
ASKER
@Pat Hartman
My sincere apologies for Number 2. I misunderstood what you were saying.
My sincere apologies for Number 2. I misunderstood what you were saying.
@Theo,
Usually it's the opposite. With a SQL BE, most of what you do is with SQL. But there are times where you'd do things with local tables possibly (reports can often be built faster working with data locally). The critical point is that if you do, the query will always be processed locally.
That's not a VBA expression, but rather a reference to an Access object. A VBA expression would be the use of Left$(), Mid$(), Date(), etc.
In this case, Access would call the JET expression service first and would resolve that to a value, then pass that off to SQL. But with a VBA call it can't because SQL has no way of calling VBA, so that forces the query to be executed locally.
Jim.
Well Number 2 on your list (Joining to local tables) is pretty much impossible to avoid in most queries.
Usually it's the opposite. With a SQL BE, most of what you do is with SQL. But there are times where you'd do things with local tables possibly (reports can often be built faster working with data locally). The critical point is that if you do, the query will always be processed locally.
I don't know of any other way to specify criteria in an easy to use format. The criteria example below is a VBA expression.
[Forms]![EditEmployeesForm]![Employe eIDSearchB ox]
That's not a VBA expression, but rather a reference to an Access object. A VBA expression would be the use of Left$(), Mid$(), Date(), etc.
In this case, Access would call the JET expression service first and would resolve that to a value, then pass that off to SQL. But with a VBA call it can't because SQL has no way of calling VBA, so that forces the query to be executed locally.
Jim.
Jim,
Do you know whether the JET expression service will resolve a tempvar before passing to SQL Server?
WHERE ID = [Tempvars]![lngID]
Dale
Do you know whether the JET expression service will resolve a tempvar before passing to SQL Server?
WHERE ID = [Tempvars]![lngID]
Dale
Yes it does. I use mostly form fields references but on occasion I use TempVars and both are resolved correctly.
Thanks, Pat.
You're welcome.
@Dale,
Great question! I don't know 100% for sure as I've never checked (I don't use tempvars) and I've never seen anything documented that says it would, but I would think so. Tempvars live in Access, not in VBA.
But I should have also mentioned that the query parser is smart enough to to figure out if a reference is a "one and done" (a single value for the entire execution of the query), or if it will need to be called once for each row (and in the case of criteria on a column with an expression, it would actually get called twice per row). That also plays in sending the SQL server side or not as well, so it's a little more complex actually than just stating the type of expression.
If you really want to be sure, turn on ODBC tracing and execute a few queries and you'll see whatever is getting sent server side.
You'll know quick enough then.
Jim.
Do you know whether the JET expression service will resolve a tempvar before passing to SQL Server?
Great question! I don't know 100% for sure as I've never checked (I don't use tempvars) and I've never seen anything documented that says it would, but I would think so. Tempvars live in Access, not in VBA.
But I should have also mentioned that the query parser is smart enough to to figure out if a reference is a "one and done" (a single value for the entire execution of the query), or if it will need to be called once for each row (and in the case of criteria on a column with an expression, it would actually get called twice per row). That also plays in sending the SQL server side or not as well, so it's a little more complex actually than just stating the type of expression.
If you really want to be sure, turn on ODBC tracing and execute a few queries and you'll see whatever is getting sent server side.
You'll know quick enough then.
Jim.
I use a local table in conjunction with a pass-thru query to retrieve and edit data. This gives me the benefits of a bound form for editing data and the pass-through for retrieving the minimum amount of data across the network. I then use a pass-thru query for writing the data back to the SQL server, although this involves more coding than with a linked table. I just edit the query syntax by writing specific parameters to a parameterized stored procedure.
When not using linked tables, one of the things you have to watch out for is multiple users working on the same record simultaneously. With linked tables, the Timestamp (RowVersion) column allows Access to identify write conflicts, but when using the technique mentioned above, you don't have that advantage. What I generally do to resolve this is to include a LastModified column in the Server side table and retrieve that value when I populate my local table. Then, prior to writing data back to the server, I check to see whether that value is the same as the value I originally retrieved. If not, you have to figure out how to deal with it, if it is, then I update the pass-thru SQL and push the data back to SS.
Dale