So we have a writable database that a lot of our product / data people use. This database has recently been migrated to Managed Instance.
We're working on getting them connected to be able to write/update DB records via Access, a functionality they've had before.
Currently, we're looking at starting them from scratch.
We can't seem to find a way to create a connection that allows for updating or changing records. The connection we're setting up allows them to insert / create new records, but the second that create goes through, even the newly created record can't be edited. We get a standard MSAccess writelock error. This is the case on EVERY table we open. Even ones that are essentially never touched.
Here's how we're connecting:
Network: The machines running msAccess are on our "main office" connection, which is peered with a vnet to the Sql Managed Instanfce. We CAN connect to the sql managed instance via ssms from this place, so it shouldn't be a networking issue (we can also insert records, so same thing)
Permissions: I've made sure that I have full sysadmin permissions via our az-sqlprod01-admin AAD group. I can do this same action in SSMS
Method:
Access > External Data > Linked Table Manager > Add:
Next Screen is Table Import, So that once that's finished we have the full table import list. We import those tables without issue.
Once there, I can bring up any table, try to edit anything, and it gives me the write-lock error at the top of this screen.
My Need: is there a way to connect MSAccess to a SQL Managed Instance without getting write-locks 100% of the time?
