I posted this in the MS SQL Server area as well but perhaps this is a better area. I apologize for the redundancy...
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?
Nevermind, folks. I figured it out (eventually). I did post this in the SQL Server area for reference there as well.
After much digging I found that the tables in question did not have their primary key migrated with them to the production server when all of the tables and data were moved from test to the production instance of the DB. 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.