Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2019, 12:42 PM   #1
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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!

CedarTree is offline   Reply With Quote
Old 09-19-2019, 01:27 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,686
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Requery just ONE row from sql server

Hi. Just a guess but try Refresh instead of Requery.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-19-2019, 03:14 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-19-2019, 03:50 PM   #4
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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.

Last edited by CedarTree; 09-19-2019 at 04:02 PM.
CedarTree is offline   Reply With Quote
Old 09-19-2019, 03:53 PM   #5
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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.
CedarTree is offline   Reply With Quote
Old 09-19-2019, 04:01 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-19-2019, 04:04 PM   #7
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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).

CedarTree is offline   Reply With Quote
Old 09-19-2019, 05:07 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,686
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Requery just ONE row from sql server

Quote:
Originally Posted by CedarTree View Post
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...
Attached Images
File Type: png refresh.png (43.0 KB, 83 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-19-2019, 05:22 PM   #9
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Requery just ONE row from sql server

Thanks. Nah, that didn't work either.
CedarTree is offline   Reply With Quote
Old 09-19-2019, 07:00 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,686
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Requery just ONE row from sql server

Quote:
Originally Posted by CedarTree View Post
Thanks. Nah, that didn't work either.
Sorry to hear that. How low did you set it? Just curious...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-19-2019, 09:50 PM   #11
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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 View Post
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).
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-20-2019, 04:22 AM   #12
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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).
CedarTree is offline   Reply With Quote
Old 09-20-2019, 04:24 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,686
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Requery just ONE row from sql server

Quote:
Originally Posted by CedarTree View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-20-2019, 05:43 AM   #14
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 261
Thanks: 39
Thanked 75 Times in 72 Posts
sonic8 is on a distinguished road
Re: Requery just ONE row from sql server

Quote:
Originally Posted by CedarTree View Post
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.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 09-20-2019, 06:04 AM   #15
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 169
Thanks: 48
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
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".

CedarTree is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server - ODBC - SQL Server Native Client Registry Bat File Rx_ SQL Server 0 01-28-2016 03:16 PM
Question How to "requery" tables linked SQL Server (ODBC) lake_tuna General 11 12-29-2015 11:42 PM
Link to SQL Server with ODBC without risking original data in SQL Server? David Ball General 1 08-21-2015 07:59 PM
SQL Server Linked Server to Oracle slow --> OpenQuery Solution Rx_ SQL Server 2 05-19-2015 08:25 AM
requery from the server hansnyc Forms 1 06-13-2007 09:02 AM




All times are GMT -8. The time now is 04:22 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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