updating data from a read-only linked table without creating a new table

I need to run some update queries nightly where the sources are read-only
tables in SQL Server. However, it seems that if any of the underlying
tables in a query are read only, the entire query is not updateable. So
what I've been doing to get around this problem is to first run a Make Table
query on the read only table to create a local table that is not read only,
and then use that local table to build the update query. It works ok, but
it adds an additional table and an additional query for each of the updates.

It would be simpler if it were possible to run an update query using a read
only linked table directly, without having to go through the extra step of
creating a new table.

I'm running these queries in VBA, so it's not the effort required; I'm just
trying to simplify my system so it's easier to maintain and modify at a
later time.

Is there any way I could run these update queries using the read only tables
directly, without having to create the intermediate tables?

Thanks in advance,

Paul

Paul

At what level is the table "read only"? Does SQL-Server restrict to this
permission? If so, you'll need to modify the SQL-Server permission first.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Paul" wrote in message
...
I need to run some update queries nightly where the sources are read-only
tables in SQL Server. However, it seems that if any of the underlying
tables in a query are read only, the entire query is not updateable. So
what I've been doing to get around this problem is to first run a Make
Table
query on the read only table to create a local table that is not read
only,
and then use that local table to build the update query. It works ok, but
it adds an additional table and an additional query for each of the
updates.

It would be simpler if it were possible to run an update query using a
read
only linked table directly, without having to go through the extra step of
creating a new table.

I'm running these queries in VBA, so it's not the effort required; I'm
just
trying to simplify my system so it's easier to maintain and modify at a
later time.

Is there any way I could run these update queries using the read only
tables
directly, without having to create the intermediate tables?

Thanks in advance,

Paul

On Sat, 23 May 2009 18:23:22 -0700, "Paul" wrote:

I need to run some update queries nightly where the sources are read-only
tables in SQL Server. However, it seems that if any of the underlying
tables in a query are read only, the entire query is not updateable. So
what I've been doing to get around this problem is to first run a Make Table
query on the read only table to create a local table that is not read only,
and then use that local table to build the update query. It works ok, but
it adds an additional table and an additional query for each of the updates.

It would be simpler if it were possible to run an update query using a read
only linked table directly, without having to go through the extra step of
creating a new table.

I'm running these queries in VBA, so it's not the effort required; I'm just
trying to simplify my system so it's easier to maintain and modify at a
later time.

Is there any way I could run these update queries using the read only tables
directly, without having to create the intermediate tables?

Thanks in advance,

Paul


Perhaps you could post the SQL of the query you're trying to update. If the
read-only table is only there because it contains fields with criteria, you
may be able to use an IN() clause rather than a JOIN.

--

John W. Vinson [MVP]

Here's the SQL I'm trying to use for the update query, John:
UPDATE dbo_Project INNER JOIN tblProject ON dbo_Project.ProjectID =
tblProject.ProjectID SET tblProject.Agency = [dbo_Project].[Agency],
tblProject.Unit = [dbo_Project].[Unit], tblProject.Notes =
[tblProject].[notes] & " - " & [dbo_Project].[AdditionalNotes] WHERE
(((tblProject.iABMS)=-1));

It would be great if there was a way to modify the SQL code so I could
eliminate the Make Table query and simplify the process.

Paul

Thanks for the suggestion, Jeff.

However, another division is sharing the data with us, and it would probably
take an act of God to get them to trust us not to change the data.

Thanks & Rgards

"John W. Vinson" wrote:

On Sat, 23 May 2009 18:23:22 -0700, "Paul" wrote:

I need to run some update queries nightly where the sources are read-only
tables in SQL Server. However, it seems that if any of the underlying
tables in a query are read only, the entire query is not updateable. So
what I've been doing to get around this problem is to first run a Make Table
query on the read only table to create a local table that is not read only,
and then use that local table to build the update query. It works ok, but
it adds an additional table and an additional query for each of the updates.

It would be simpler if it were possible to run an update query using a read
only linked table directly, without having to go through the extra step of
creating a new table.

I'm running these queries in VBA, so it's not the effort required; I'm just
trying to simplify my system so it's easier to maintain and modify at a
later time.

Is there any way I could run these update queries using the read only tables
directly, without having to create the intermediate tables?

Thanks in advance,

Paul


Perhaps you could post the SQL of the query you're trying to update. If the
read-only table is only there because it contains fields with criteria, you
may be able to use an IN() clause rather than a JOIN.

--

John W. Vinson [MVP]