I cannot add, update or delete records in a SQL Server database using ADP/ADE but I can within another database on the same server.
I have two databases. One is the test environment and the other is production. Both database reside on the same server but they have unique logins so as not to allow an admin in the test environment to automatically be an admin in production.
I can do everything and anything necessary through Enterprise Manager to both databases. I can do everything necessary to an Access ADP/ADE application in the test environment (add, update, delete) but I cannot do the same in the production database. I can connect and get a valid connection but the tables do not allow me to update or add rows to tables through the ADP/ADE application.
Given that the only difference to the application is which database it connects to I am left with the conclusion that there is something different to the login in the production environment that is not allowing the ADP application appropriate permissions. Can anyone please help or at least direct me to an answer to correct this problem?
Well... I suppose this is what happens when you aren't in charge of the data and it is migrated by someone else.
After much digging I found that the tables in question did not have their primary key migrated with them to the production server. If the table did not have a primary key defined then ADP/ADE would not allow a user to add rows.
This was structural, not security. Hopefully someone else who runs across a similar problem in the future will find this information and more quickly correct their issue.