🚀 go-pugleaf

In article <DCJ76.170296$eT4.13196254@nnrp3.clara.net>, TEST
TESTPOP_Server=pop.freeuk.net <testx@freeuk.com> writes
>I am running MS Access 97 with an SQL Server 7 attached Table.
>I have an Access Form open based on an Access Query that shows a quantity of
>records from the attached SQL Server Table.
>I can update, delete and add records.
>The problem is that when a Field is changed in one record several other
>records have to be updated.  The Field which is updated is a number and is
>part of the Primary Key.  I am using the same DAO code that worked fine in
>Access when the table was an Access Table - the updates happenned and you
>could see the numbers in other records change.  While the code runs fine
>with the attached SQL Server Table the updates are not visible unless a
>requery operation is performed which is unacceptable because of the delay
>this introduces to user activity and the fact that the cursor moves to the
>first record.  In addition, if I attempt to edit a record where the number
>Field has been changed I get error messages and #Deleted appears in the
>record Fields.
>How do I resolve this problem?
>I want the changes in the number Fields of other records to show immediately
>and the records to be available for editing - just like when the underlying
>Table was in Access.
>
You'll probably need to change the ODBC Refresh Interval. This can be
set between 1 and 32766 seconds. I just looked in my Access setup and
it's set to 1500, so you would only get ODBC data refreshed every 25
minutes!
-- 
Albert Marshall

Agree with Steve on this one. My approach is the same in the way. In
A97 I use PassTrough query to execute my stored procedures, let the SQL
Server manage DML. I also use PassTrough for viewing records again by
executing procedures. Linked tables, I dont use any more to much
maintenance and overhead with them.


In article <Vh0M9IGjc4X6Ewpm@execfrog.demon.co.uk>,
  Albert Marshall <albert@execfrog.demon.co.uk> wrote:
> In article <DCJ76.170296$eT4.13196254@nnrp3.clara.net>, TEST
> TESTPOP_Server=pop.freeuk.net <testx@freeuk.com> writes
> >I am running MS Access 97 with an SQL Server 7 attached Table.
> >I have an Access Form open based on an Access Query that shows a
quantity of
> >records from the attached SQL Server Table.
> >I can update, delete and add records.
> >The problem is that when a Field is changed in one record several
other
> >records have to be updated.  The Field which is updated is a number
and is
> >part of the Primary Key.  I am using the same DAO code that worked
fine in
> >Access when the table was an Access Table - the updates happenned
and you
> >could see the numbers in other records change.  While the code runs
fine
> >with the attached SQL Server Table the updates are not visible
unless a
> >requery operation is performed which is unacceptable because of the
delay
> >this introduces to user activity and the fact that the cursor moves
to the
> >first record.  In addition, if I attempt to edit a record where the
number
> >Field has been changed I get error messages and #Deleted appears in
the
> >record Fields.
> >How do I resolve this problem?
> >I want the changes in the number Fields of other records to show
immediately
> >and the records to be available for editing - just like when the
underlying
> >Table was in Access.
> >
> You'll probably need to change the ODBC Refresh Interval. This can be
> set between 1 and 32766 seconds. I just looked in my Access setup and
> it's set to 1500, so you would only get ODBC data refreshed every 25
> minutes!
> --
> Albert Marshall
>


Sent via Deja.com
http://www.deja.com/

"TEST TESTPOP_Server=pop.freeuk.net" <testx@freeuk.com> wrote:

>The Field which is updated is a number and is
>part of the Primary Key.  

In my not so humble opinion <smile> this is poor database design.  The
primary key should be a number which has no meaning, ie like the
AutoNumbers we use in Access.  All child tables reference the parent
table using this non meaningful number.

I also realise that you likely don't have any control now over the
design of the tables. 

Tony
----
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm