Requery just ONE row from sql server (1 Viewer)

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
Hello.

I have data on SQL server. Say for example tblPeople with 2 fields:
Name = obvious
Invited = flag whether they were invited or not
Let's say there's two rows, for John and Sally.

In Access, I've saved a pass-through query to quickly grab tblPeople= qryTest. On the main form, I have a subform(datasheet) that refers to qryTest, and shows me John and Sally. This recordset is not updateable, I presume b/c it's relying on a pass-through query.

When I press a button on the main form, I send an pass-through update query to change the Invited flag. On the server, I can see the flag has been updated. I want to then update just the ONE row I'm (say John's row) in the datasheet to reflect the update, without updating the whole datasheet. So I've been trying the requery method, with no luck. It's as if I have to run the whole query again, but I'm trying to avoid that. Thoughts? Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:07
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess but try Refresh instead of Requery.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 19, 2002
Messages
42,970
Not sure why you are doing this with a pass through query but if you want a query to update only specific records, the query needs a WHERE clause to allow it to identify the rows to update.

Seems to me that this is more likely an interactive action between the user and a form. The user finds the record he wants to update and makes the change on the bound form. Access saves the change automatically when the form is closed or moved to a new record.

Access is a RAD (Rapid Application Development) tool. the biggest benefit of using Access is using bound forms. If you are not going to use bound forms, you should probably be using some other platform that doesn't come with the baggage that Access comes with.

I've been using Access since the early 90's and my very first application (and the one that sold me on Access) used forms bound to linked DB2 tables. It was like a miracle. I could update data on our mainframe using an Access form. Since then,I've created hundreds of application most of which use some ODBC BE such as SQL Server, Oracle, DB2, Sybase, etc. In all cases, I use linked tables and bound forms. There are some situations where I do use pass-through queries and stored procedures but they are few and far between. The thing you need to understand is that ALL forms should be bound to queries with selection criteria. That way, when the user calls up the record for client 123, Access sends a query to the server (essentially passing through the criteria) and the server locates the single record and that is what it returns to the form. If you bind the forms to tables or to queries with no WHERE clauses, you force Access to work inefficiently and ask the server to return all rows in the table.

I typically only use pass-through queries to run bulk update and delete queries. Sometimes I create views to speed up certain joins and then I link to the views.
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
I may not being as clear as I should be. I think a similar situation would be this... if I have a linked table in DB(1) with the source table actually in DB(2)... if I update a row in DB(2), how can I, in a Form in DB(1), see that update by just refreshing one row. I don't want to refresh the whole form if possible.
 
Last edited:

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
And Refresh reloads the table from scratch, which calls OnCurrent again, etc., which I don't want.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 19, 2002
Messages
42,970
So you have multiple people making changes to the same record at the same time??? Not common but possible. If you leave a form open, Access, in the background, refreshes the recordset every x minutes. I don't know what the default is but you can change it or if that isn't sufficient, you can add a Timer event to the form to make the refresh happen more frequently. And you can press the refresh button in the Ribbon to force it to happen immediately.

Most people close a form when they are done with it rather than leaving it open. I did have one application where the users were taking orders and they all needed to be working with up to the second inventory. This was a wholesale florist and they were dealing with live goods with short shelf lives. I used a timer on the form so they could leave the inventory form open. I also forced a requery when they started a new order just to make sure they were looking at the most current quantities available. You don't want to promise to deliver red roses when all you have are white because someone just bought your last reds.

You don't refresh a single row. You refresh the form's RecordSource. What is the problem you are experiencing?
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
The data is getting updated on SQL Server so that's working.
When I Form.Refresh, nothing happens (I don't see the data change on the form as I would like to). When I Form.Requery (or hit F9 or F5), it reloads the Recordset, which takes me to the first row... I want to stay on the original row and not trigger OnCurrent.

(People can't make changes to the same record... I have coding that prevents that).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:07
Joined
Oct 29, 2018
Messages
21,358
And Refresh reloads the table from scratch, which calls OnCurrent again, etc., which I don't want.
Just thought it was worth a try. How about using this settings then? You can try setting it to a lower number.






Hope it helps...
 

Attachments

  • refresh.png
    refresh.png
    43 KB · Views: 683

Gasman

Enthusiastic Amateur
Local time
Today, 16:07
Joined
Sep 21, 2011
Messages
14,044
Getting back to the original row is easy enough, though not sure about not triggering OnCurrent.

The data is getting updated on SQL Server so that's working.
When I Form.Refresh, nothing happens (I don't see the data change on the form as I would like to). When I Form.Requery (or hit F9 or F5), it reloads the Recordset, which takes me to the first row... I want to stay on the original row and not trigger OnCurrent.

(People can't make changes to the same record... I have coding that prevents that).
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
I set it to like 5 seconds... something quite low just to see.
I think my workaround is not to use a pass-through Select query but a regular query, which seems to be fast enough (PT would be faster though).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:07
Joined
Oct 29, 2018
Messages
21,358
I set it to like 5 seconds... something quite low just to see.
I think my workaround is not to use a pass-through Select query but a regular query, which seems to be fast enough (PT would be faster though).
Good point. Maybe PTs don't get auto refreshed. Not sure...
 

sonic8

AWF VIP
Local time
Today, 17:07
Joined
Oct 27, 2015
Messages
998
When I press a button on the main form, I send an pass-through update query to change the Invited flag. On the server, I can see the flag has been updated. I want to then update just the ONE row I'm (say John's row) in the datasheet to reflect the update, without updating the whole datasheet.
I guess, it's not going to work with a Pass-Through-Query.
Create a view with the main SQL statement from the Pass-Through-Query, link the view to Access and use that instead. A Refresh should work then.
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
So I saved the pass -through as a query (qryTemp) and the Form uses that qry as it's recordsource... that wasn't working. Are you thinking of something else? What do you mean by "View".
 

sonic8

AWF VIP
Local time
Today, 17:07
Joined
Oct 27, 2015
Messages
998
What do you mean by "View".
A view is similar to a saved select-query in Access but it is stored in the SQL Server database. See CREATE VIEW for further details.

You can link a view to Access as if it were a table. For the linked view to be updateable you need to set its unique (primary) key either manually during the linking operation or by code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 19, 2002
Messages
42,970
Why not try linking the table and using a standard Access query rather than a pass-through query?
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
Yeah I tried the standard query... that works BUT if you do a search/find, it takes FOREVER as compared to a P-T query. I'm going to explore the View idea.
 

CedarTree

Registered User.
Local time
Today, 12:07
Joined
Mar 2, 2018
Messages
404
So I got the view linked using:

Code:
DoCmd.TransferDatabase acLink, "ODBC Database"...

... but how to I program it to set up the indexes automatically so that it doesn't prompt the user every time I relink it?
 

isladogs

MVP / VIP
Local time
Today, 16:07
Joined
Jan 14, 2017
Messages
18,186
Yeah I tried the standard query... that works BUT if you do a search/find, it takes FOREVER as compared to a P-T query. I'm going to explore the View idea.

If you index the field(s) being searched, the standard select query should be almost instantaneous. I very much doubt a P-T query would be noticeably faster
 

Users who are viewing this thread

Top Bottom