Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Forms (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=9)
-   -   Requery just ONE row from sql server (https://www.access-programmers.co.uk/forums/showthread.php?t=306930)

CedarTree 09-19-2019 12:42 PM

Requery just ONE row from sql server
 
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 09-19-2019 01:27 PM

Re: Requery just ONE row from sql server
 
Hi. Just a guess but try Refresh instead of Requery.

Pat Hartman 09-19-2019 03:14 PM

Re: Requery just ONE row from sql server
 
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 09-19-2019 03:50 PM

Re: Requery just ONE row from sql server
 
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.

CedarTree 09-19-2019 03:53 PM

Re: Requery just ONE row from sql server
 
And Refresh reloads the table from scratch, which calls OnCurrent again, etc., which I don't want.

Pat Hartman 09-19-2019 04:01 PM

Re: Requery just ONE row from sql server
 
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 09-19-2019 04:04 PM

Re: Requery just ONE row from sql server
 
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 09-19-2019 05:07 PM

Re: Requery just ONE row from sql server
 
1 Attachment(s)
Quote:

Originally Posted by CedarTree (Post 1641094)
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.



https://www.access-programmers.co.uk...1&d=1568941598


Hope it helps...

CedarTree 09-19-2019 05:22 PM

Re: Requery just ONE row from sql server
 
Thanks. Nah, that didn't work either.

theDBguy 09-19-2019 07:00 PM

Re: Requery just ONE row from sql server
 
Quote:

Originally Posted by CedarTree (Post 1641102)
Thanks. Nah, that didn't work either.

Sorry to hear that. How low did you set it? Just curious...

Gasman 09-19-2019 09:50 PM

Re: Requery just ONE row from sql server
 
Getting back to the original row is easy enough, though not sure about not triggering OnCurrent.

Quote:

Originally Posted by CedarTree (Post 1641097)
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 09-20-2019 04:22 AM

Re: Requery just ONE row from sql server
 
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 09-20-2019 04:24 AM

Re: Requery just ONE row from sql server
 
Quote:

Originally Posted by CedarTree (Post 1641163)
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 09-20-2019 05:43 AM

Re: Requery just ONE row from sql server
 
Quote:

Originally Posted by CedarTree (Post 1641052)
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 09-20-2019 06:04 AM

Re: Requery just ONE row from sql server
 
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".


All times are GMT -8. The time now is 02:14 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World