Help! Need to update linked SQL table in Acc07

[George Atkins]

I have several linked MS SQL SVR tables. I need to update multiple records in
one of the tables, but I'm having a problem. First, the basics:

Main table is Enrollments. it contains student records over many years, so
each student can have multple records, identified by enrollment ID (key) or
other data. I need to update one record for each selected student.

Qry902NonPubList - a query of other linked SQL tables producing a list of
student records that need to be modified.

Qry902NonPubList2008 - a query of linked SQL tables producing the values
that need to be entered into each student's record.

In fact, the two queries each reference Enrollments table to find this other
information, so it's some kind if incestuous relationship! Anyway...

I can update the enrollment table through a query if it is the only object
in the query and I manually enter the criteria. But when I link in the two
queries, I get the dreaded "operation must use an updateable query." I
understand that there are situations where queries cannot update SQL tables
(such as this!!), but I need a good workaround/solution.

Here is the SQL version of my query:

UPDATE (qry902NonPubList2008 INNER JOIN qry902NonPubList ON
qry902NonPubList2008.studentNumber = qry902NonPubList.studentNumber) INNER
JOIN dbo_Enrollment ON qry902NonPubList.enrollmentID =
dbo_Enrollment.enrollmentID SET dbo_Enrollment.startComments =
[qry902nonpublist2008].[startcomments]
WHERE (((dbo_Enrollment.startComments) Is Null));

I would be happy to e-mail a picture, if necessary. Any ideas how I should
handle this? I need to update a single record for each of nearly 600
students. Thanks for any ideas.

 

[Jerry Whittle]

You're going to need subqueries if it is going to work at all. What I have
created below is a WAG and probably will NOT work right. I'd be shocked if it
did actually. Test on a backup. Also there's a really good possibility that
the = will need to be converted to IN or EXISTS formats depending on the type
of error returned.

UPDATE dbo_Enrollment
SET dbo_Enrollment.startComments =
(SELECT DISTINCT A.startcomments
FROM qry902nonpublist2008 A
WHERE A.studentNumber =
(SELECT DISTINCT B.studentNumber
FROM qry902NonPubList B))
WHERE dbo_Enrollment.startComments Is Null
AND dbo_Enrollment.enrollmentID =
(SELECT DISTINCT PL.enrollmentID
FROM qry902NonPubList PL) ;

I'm also worried about the 2008 and 2009 in the query names. Hopefully that
means the query limits records to a certain year. If there are different
tables for each year, that's a real, basic problem that I'm guessing you have
no control over.

 

[George Atkins]

Jerry,
Thanks for the suggestion! I was afraid the task was too muddled to even
figure out. I don't want you to feel you wasted your time, because I am going
to go through what you wrote.

But a colleague and I just got through with a workaround. It seems that
Access has trouble resolving complicated relationships in queries that are
linked to updating SQL tables. And those linked queries are, indeed, based on
several tables. What made it all work was to do a query of the two queries in
my problem and build a temporary table out of their combined results. So the
table contains the current year records with the missing values from the
prior year. Then I made another query and linked it to the SQL enrollment
table. When I did this, I was easily able to update the Enrollment record
fields! That is something for me to remember.

But you are right: the two queries actually do reference the "enrollment"
table (and others) on their own, extracing records from prior years to obtain
certain values. So my complex query referenced the enrollment table 3
different ways. No wonder it was confused! Many thanks, though, for giving me
another way to think about this.

You're going to need subqueries if it is going to work at all. What I have
created below is a WAG and probably will NOT work right. I'd be shocked if it
did actually. Test on a backup. Also there's a really good possibility that
the = will need to be converted to IN or EXISTS formats depending on the type
of error returned.

UPDATE dbo_Enrollment
SET dbo_Enrollment.startComments =
(SELECT DISTINCT A.startcomments
FROM qry902nonpublist2008 A
WHERE A.studentNumber =
(SELECT DISTINCT B.studentNumber
FROM qry902NonPubList B))
WHERE dbo_Enrollment.startComments Is Null
AND dbo_Enrollment.enrollmentID =
(SELECT DISTINCT PL.enrollmentID
FROM qry902NonPubList PL) ;

I'm also worried about the 2008 and 2009 in the query names. Hopefully that
means the query limits records to a certain year. If there are different
tables for each year, that's a real, basic problem that I'm guessing you have
no control over.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have several linked MS SQL SVR tables. I need to update multiple records in
one of the tables, but I'm having a problem. First, the basics:

Main table is Enrollments. it contains student records over many years, so
each student can have multple records, identified by enrollment ID (key) or
other data. I need to update one record for each selected student.

Qry902NonPubList - a query of other linked SQL tables producing a list of
student records that need to be modified.

Qry902NonPubList2008 - a query of linked SQL tables producing the values
that need to be entered into each student's record.

In fact, the two queries each reference Enrollments table to find this other
information, so it's some kind if incestuous relationship! Anyway...

I can update the enrollment table through a query if it is the only object
in the query and I manually enter the criteria. But when I link in the two
queries, I get the dreaded "operation must use an updateable query." I
understand that there are situations where queries cannot update SQL tables
(such as this!!), but I need a good workaround/solution.

Here is the SQL version of my query:

UPDATE (qry902NonPubList2008 INNER JOIN qry902NonPubList ON
qry902NonPubList2008.studentNumber = qry902NonPubList.studentNumber) INNER
JOIN dbo_Enrollment ON qry902NonPubList.enrollmentID =
dbo_Enrollment.enrollmentID SET dbo_Enrollment.startComments =
[qry902nonpublist2008].[startcomments]
WHERE (((dbo_Enrollment.startComments) Is Null));

I would be happy to e-mail a picture, if necessary. Any ideas how I should
handle this? I need to update a single record for each of nearly 600
students. Thanks for any ideas.

 

[George Atkins]

Jerry,
Thanks for the suggestion! I was afraid the task was too muddled to even
figure out. I don't want you to feel you wasted your time, because I am going
to go through what you wrote.

But a colleague and I just got through with a workaround. It seems that
Access has trouble resolving complicated relationships in queries that are
linked to updating SQL tables. And those linked queries are, indeed, based on
several tables. What made it all work was to do a query of the two queries in
my problem and build a temporary table out of their combined results. So the
table contains the current year records with the missing values from the
prior year. Then I made another query and linked it to the SQL enrollment
table. When I did this, I was easily able to update the Enrollment record
fields! That is something for me to remember.

But you are right: the two queries actually do reference the "enrollment"
table (and others) on their own, extracing records from prior years to obtain
certain values. So my complex query referenced the enrollment table 3
different ways. No wonder it was confused! Many thanks, though, for giving me
another way to think about this.

You're going to need subqueries if it is going to work at all. What I have
created below is a WAG and probably will NOT work right. I'd be shocked if it
did actually. Test on a backup. Also there's a really good possibility that
the = will need to be converted to IN or EXISTS formats depending on the type
of error returned.

UPDATE dbo_Enrollment
SET dbo_Enrollment.startComments =
(SELECT DISTINCT A.startcomments
FROM qry902nonpublist2008 A
WHERE A.studentNumber =
(SELECT DISTINCT B.studentNumber
FROM qry902NonPubList B))
WHERE dbo_Enrollment.startComments Is Null
AND dbo_Enrollment.enrollmentID =
(SELECT DISTINCT PL.enrollmentID
FROM qry902NonPubList PL) ;

I'm also worried about the 2008 and 2009 in the query names. Hopefully that
means the query limits records to a certain year. If there are different
tables for each year, that's a real, basic problem that I'm guessing you have
no control over.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have several linked MS SQL SVR tables. I need to update multiple records in
one of the tables, but I'm having a problem. First, the basics:

Main table is Enrollments. it contains student records over many years, so
each student can have multple records, identified by enrollment ID (key) or
other data. I need to update one record for each selected student.

Qry902NonPubList - a query of other linked SQL tables producing a list of
student records that need to be modified.

Qry902NonPubList2008 - a query of linked SQL tables producing the values
that need to be entered into each student's record.

In fact, the two queries each reference Enrollments table to find this other
information, so it's some kind if incestuous relationship! Anyway...

I can update the enrollment table through a query if it is the only object
in the query and I manually enter the criteria. But when I link in the two
queries, I get the dreaded "operation must use an updateable query." I
understand that there are situations where queries cannot update SQL tables
(such as this!!), but I need a good workaround/solution.

Here is the SQL version of my query:

UPDATE (qry902NonPubList2008 INNER JOIN qry902NonPubList ON
qry902NonPubList2008.studentNumber = qry902NonPubList.studentNumber) INNER
JOIN dbo_Enrollment ON qry902NonPubList.enrollmentID =
dbo_Enrollment.enrollmentID SET dbo_Enrollment.startComments =
[qry902nonpublist2008].[startcomments]
WHERE (((dbo_Enrollment.startComments) Is Null));

I would be happy to e-mail a picture, if necessary. Any ideas how I should
handle this? I need to update a single record for each of nearly 600
students. Thanks for any ideas.