A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using ODBC Connection Query Error: Non-Updatable Query



 
 
Thread Tools Display Modes
  #1  
Old February 15th, 2007, 08:52 PM posted to microsoft.public.access.queries
rmcompute
external usenet poster
 
Posts: 94
Default Using ODBC Connection Query Error: Non-Updatable Query

A query was running using a linked table to an Access database. The query
linked an access table from the current database and uptated that table with
data from the linked table. We then used an ODBC connection to linke to SQL
Server to obtain the required information to update the Access table in the
current database. An error was displayed: Non-Updatable Query. We then
imported the table and the query worked. Is there a way to modify the
connection so we can still use linked tables to the ODBC data source ?
  #2  
Old February 15th, 2007, 11:42 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Using ODBC Connection Query Error: Non-Updatable Query

Does Access recognize a primary key in the linked ODBC table? If not, you
will need to create one. This can be done with a data definition query within
Access.
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

A query was running using a linked table to an Access database. The query
linked an access table from the current database and uptated that table with
data from the linked table. We then used an ODBC connection to linke to SQL
Server to obtain the required information to update the Access table in the
current database. An error was displayed: Non-Updatable Query. We then
imported the table and the query worked. Is there a way to modify the
connection so we can still use linked tables to the ODBC data source ?

  #3  
Old February 16th, 2007, 09:13 PM posted to microsoft.public.access.queries
rmcompute
external usenet poster
 
Posts: 94
Default Using ODBC Connection Query Error: Non-Updatable Query

I created a primary key on the SQL table but the query still failed with the
same message. The code is listed below. Local Custsupp File is the SQL
table. The primary key was created on field "code". Is there something else
that needs to be done ?

UPDATE [Contract Renewal Analysis] INNER JOIN [Local Custsupp File] ON
[Contract Renewal Analysis].CODE = [Local Custsupp File].code SET [Contract
Renewal Analysis].[Aggregate Billing] = [Local Custsupp File]![consbill];




"Duane Hookom" wrote:

Does Access recognize a primary key in the linked ODBC table? If not, you
will need to create one. This can be done with a data definition query within
Access.
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

A query was running using a linked table to an Access database. The query
linked an access table from the current database and uptated that table with
data from the linked table. We then used an ODBC connection to linke to SQL
Server to obtain the required information to update the Access table in the
current database. An error was displayed: Non-Updatable Query. We then
imported the table and the query worked. Is there a way to modify the
connection so we can still use linked tables to the ODBC data source ?

  #4  
Old February 16th, 2007, 10:51 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Using ODBC Connection Query Error: Non-Updatable Query

Where did you create the primary key on the SQL table? If you did this on the
actual server, did you relink the table so Access can see the primary key? If
you open the linked table in design view, is the primary key identified?
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

I created a primary key on the SQL table but the query still failed with the
same message. The code is listed below. Local Custsupp File is the SQL
table. The primary key was created on field "code". Is there something else
that needs to be done ?

UPDATE [Contract Renewal Analysis] INNER JOIN [Local Custsupp File] ON
[Contract Renewal Analysis].CODE = [Local Custsupp File].code SET [Contract
Renewal Analysis].[Aggregate Billing] = [Local Custsupp File]![consbill];




"Duane Hookom" wrote:

Does Access recognize a primary key in the linked ODBC table? If not, you
will need to create one. This can be done with a data definition query within
Access.
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

A query was running using a linked table to an Access database. The query
linked an access table from the current database and uptated that table with
data from the linked table. We then used an ODBC connection to linke to SQL
Server to obtain the required information to update the Access table in the
current database. An error was displayed: Non-Updatable Query. We then
imported the table and the query worked. Is there a way to modify the
connection so we can still use linked tables to the ODBC data source ?

  #5  
Old February 21st, 2007, 12:02 AM posted to microsoft.public.access.queries
rmcompute
external usenet poster
 
Posts: 94
Default Using ODBC Connection Query Error: Non-Updatable Query

When I re-linked the table, it worked. Thanks for the help.


"Duane Hookom" wrote:

Where did you create the primary key on the SQL table? If you did this on the
actual server, did you relink the table so Access can see the primary key? If
you open the linked table in design view, is the primary key identified?
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

I created a primary key on the SQL table but the query still failed with the
same message. The code is listed below. Local Custsupp File is the SQL
table. The primary key was created on field "code". Is there something else
that needs to be done ?

UPDATE [Contract Renewal Analysis] INNER JOIN [Local Custsupp File] ON
[Contract Renewal Analysis].CODE = [Local Custsupp File].code SET [Contract
Renewal Analysis].[Aggregate Billing] = [Local Custsupp File]![consbill];




"Duane Hookom" wrote:

Does Access recognize a primary key in the linked ODBC table? If not, you
will need to create one. This can be done with a data definition query within
Access.
--
Duane Hookom
Microsoft Access MVP


"rmcompute" wrote:

A query was running using a linked table to an Access database. The query
linked an access table from the current database and uptated that table with
data from the linked table. We then used an ODBC connection to linke to SQL
Server to obtain the required information to update the Access table in the
current database. An error was displayed: Non-Updatable Query. We then
imported the table and the query worked. Is there a way to modify the
connection so we can still use linked tables to the ODBC data source ?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.