My Access db has SQL Svr linked tables, via ODBC data source.
In Access the tables that have foreign keys cannot be edited (no updates, no new records).

The other linked tables can be edited in Access.

If I use SQL Svr Query analyzer on same PC, I can update / insert records in any table.

How do I get Access to be able to edit those tables with foreign keys ??

JKing

__________________
Thanks,
J K

Do your non-updateable tables have a primary key field?

If the "linked tables" are views, you may not be able to update them since they contain more than one table. This can be a limitation in SQL server views.

Also, the credentials your ODBC connection is using may not allow updates/deletes etc. Create another ODBC connection using your sa credentials and see if this helps.

mmcdonal

Yes, the non-updateable tables have a primary key field. And so do the updateable tables.
JK


>>
>> Do your non-updateable tables have a primary key field?
>>

>>
>>the credentials your ODBC connection is using may not allow updates/deletes etc.
>>

That is what puzzles me -- all the tables are in same SQL Svr db, and Access is linked to those tables thru just one ODBC connection. The tables with foreign keys can't be updated, but the tables that ARE the foreign key links CAN be updated.
Nevertheless, when I return to that system, I'll change the ODBC connection to SQL auth, user sa.

Thanks,

JK

Are you using views? This will prevent updates when there is more than one table in the view.

mmcdonal

I also just thought it may be an ANSI SQL standards issue. Access uses ANSI 89, and SQL Server 2000 uses ANSI 92, I think, so some syntax may be causing a problem.

mmcdonal

>> ...Are you using views? ...

No views yet.

I add SQL Svr views to a system only after the simpler system works with all the queries in Access. Then I can migrate items to SQL Svr for performance and other good reasons. So now Access is linked to SQL tables.

Thanks,
JK

Ouch, that sort of defeats one of the purposes of working with a SQL back end. If you are pulling ALL of the data anyway (and then using local queries...) I bet that IS slow.

I would suggest, just for giggles, creating one of your queries in SQL Server, and then using the view as the linked object.

I would also do the updates by sending individual updates to the server using code, not linked tables.

mmcdonal

Solution found:
  The SQL Svr table had an ID column type bigint.
Access doesn't recognize bigint, only int.
After the ID column was changed to type int,
in Access - had to delete the linked table and recreate the link.
Now table edits and record inserts are allowed in Access.
:-)

JKing




Thanks,
J K