|
Subject:
|
Insert record disabled
|
|
Posted By:
|
jking
|
Post Date:
|
12/15/2005 8:06:30 PM
|
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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
12/16/2005 12:59:29 AM
|
Do your non-updateable tables have a primary key field?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/16/2005 7:24:30 AM
|
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
|
|
Reply By:
|
jking
|
Reply Date:
|
12/16/2005 1:41:52 PM
|
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?
>>
|
|
Reply By:
|
jking
|
Reply Date:
|
12/16/2005 1:48:41 PM
|
>>
>>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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/16/2005 1:58:39 PM
|
Are you using views? This will prevent updates when there is more than one table in the view.
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/16/2005 2:00:22 PM
|
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
|
|
Reply By:
|
jking
|
Reply Date:
|
12/16/2005 7:04:56 PM
|
>> ...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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/17/2005 4:11:13 PM
|
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
|
|
Reply By:
|
jking
|
Reply Date:
|
1/10/2006 3:31:32 PM
|
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
|
