in order to set the primary key (critical for other functions/ relationships). So far so good.
Now we've moved the tables to a backend database ("BE_DB") for multiple front-end users. The code was changed so that it copies the linked file data to the BE_DB.
The following code was added to Alter the backend table for the primary key at the backend table:
Dim dbBackend As Database
Dim strBackend As String
Dim strDDL As String
strBackend = "\\NetworkPublicFolder\InventoryManagement_be.accdb"
Set dbBackend = OpenDatabase(strBackend)
strDDL = "ALTER TABLE tblLineItem ADD CONSTRAINT PrimaryKey PRIMARY KEY (ItemID)"
dbBackend.Execute strDDL, dbFailOnError
Set dbBackend = Nothing
Okay, so this works perfectly EXCEPT that we immediately get error 3112 "no read permission" on other related tables. If the data is copied and only the remote Alter code is skipped, then no 3112 error.
I know that 3112 is often a corruption issue, but if I skip this remote ALTER code, then everything works perfectly (except we lose the critical relationships). So a corruption issue doesn't seem to fit.
It sounds like we're not opening/ closing the BE_DB quite right, but I can't see the error.
Thanks Sinndho. I got a "Expected Function or Variable" error upon compile.
Another solution to the original issue here would be to be able to run the MakeTable query without losing the Primary Key property on the id field of the new table. Is there a way of doing that? If that were possible, then I can avoid this horsing around trying to Alter Table of the new remote table.