What are the downsides to lowering ODBC Refresh Interval?

Why the huge jump from 1500 (25 mins, I believe) down to 1?
:-O

Instead of jumping from 1500 to 1, why not decrease it "gradually" until issues start occurring, then bump it back up a bit.

;-)

Also worth noting is that setting this to zero is not "Instant" refreshes, as you might think. This results in "No" refreshes.

;-)

JeffCoachman

LSM Consulting & Jeff:

Thank you for your responses to this inquery.  I was aware that '0' meant no refreshes.  What I don't understand is why the default is so high.   1500 seconds is 25minutes!  In a multi-user environment, does this mean the users have a potential of waiting 25 minutes before another user's data is reflected to their screen?

The reason I picked '1' is that the users do not want to see this error message.  It is not unusual for our users to be editing record 1, move to record 2 to copy something then back to record 1 to paste the value.  So the refresh rate needs to be something low enough so that when the user moves from record 1 to record 2 and back again which I believe to be somewhere near 5 seconds--or less for our Speedy Gonzoles QA tester :)

Our database is very small.  All records in all tables do not total more than 20K and on average each table is less than 750 records.  I can not detect any performance issues with a refresh rate of 1.

Thanks again for your responses.

Perhaps I misunderstood the issue.

Are you saying that
1. Everything was fine with a refresh interval of 1500
2. You then made a mod to the table and you started getting errors, (again, at 1500)
3. You then set the rate to 1, and the issue was resolved?
Please confirm.



LSM will have more detailed info on this...

A refresh of 1 second will force Access to make a call to the Source for *All* the data.
Even in a small source this may cause more network traffic than you  (Or especially the Network Admin) may want.
;-)
Network Hardware, network speed, server performance, physical distance from the server, ...etc...also may play a role in this.

Remember, the source may be "small" now, but perhaps not in a few months or years
:-O

In other words 1 second may be too short a time to pull in all the data before the next call is made.
Possibly introducing a totally different set of errors...

But again, let's see what LSM says on this...

;-)

Jeff

Jeff:

Re your question above, yes that is the scenario.  Prior to introduction of triggers, the only way a record changed was if the user changed it.  So if a user was on record 1, made a change, went to record 2 and then back to record 1 for another change, record 1 reflected the original change for the user.  After the introduction of the trigger, whenever a user makes a record change, SQL Server also makes a record change which is not reflected back to the user until the end of the refresh interval.  So if the user needs to make another change to the same record prior to the end of the refresh interval, the error message appears.  

Setting a refresh interval of 1 resolves the issue.  Whether or not the issue is resolved for a refresh rate higher than 1 is a function of the speed of which the user  moves back and forth between changing records and returning to changed records.

The information you have provided is helpful as we are trying to identify the risks prior to releasing this change into production and to ensure all the right parties are involved in the decision making process of what to set the refresh rate to.

Thanks,

Sue

Sorry, I am no expert on triggers in SQL Server, so I can't comment.

I'll wait and see if LSM posts anything further...

Jeff

The behaviour you're describing is unusual (or at least the proposed reason should be).
Obviously if every action performed by a trigger on a table caused concurrency problems in Access then it would have to be addressed by design.

Can you provide the actual schema of your table (all of it) and the trigger definition (which I would expect to actually be fairly innocuous).

You might want to add the tried, tired, tested, proven and disproven method of adding a TimeStamp field to prevent innaccurate update checking from floating point and non-Jet equivalent fields.
That's more likely to play a part.

Cheers.

I'm happy to provide the actual schema of one of the tables and the trigger.  Attached as a code snippet is the text from SQL Script Preview which provides you with the table name, field names, types, etc.  Following that is the table constraints and trigger.  Let me know if you need anything else.  thanks for your help.

CREATE TABLE [dbo].[tblPeople] (
 [People_ID] [int] IDENTITY (1, 1) NOT NULL ,
 [Prefix] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [First_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Middle_Int] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Last_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Suffix] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Degrees] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Business] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Address1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Address2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [City] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Zip] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Phone] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Fax] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Email] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Addl_Contact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Cleanup notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [byteReportFormat] [smallint] NULL ,
 [RecModDtTm] [datetime] NULL ,
 [RecModBy] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [RecAddedDtTm] [datetime] NULL ,
 [RecAddedBy] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[tblPeople] WITH NOCHECK ADD 
 CONSTRAINT [DF__tblPeople__RecAd__69FBBC1F] DEFAULT (getdate()) FOR [RecAddedDtTm],
 CONSTRAINT [DF_tblPeople_RecAddedBy] DEFAULT (suser_sname()) FOR [RecAddedBy],
 CONSTRAINT [aaaaatblPeople_PK] PRIMARY KEY  NONCLUSTERED 
 (
  [People_ID]
 ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
 
CREATE TRIGGER [dbo].[trg_tblPeople_LastUpdated] ON dbo.tblPeople 
FOR UPDATE
AS
UPDATE [dbo].[tblPeople]
SET RecModDtTm = getdate(),
RecModBy = suser_sname()
WHERE People_ID IN (Select People_ID From INSERTED) 
  

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44: