Using ODBC Connection Query Error: Non-Updatable Query

[Guest]

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 ?

 

[Guest]

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.

 

[Guest]

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];

 

[Guest]

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

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];

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.

 

[Guest]

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

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

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];

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


:

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 ?