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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|