Problems Relinking tables via code

[dscline]

I have an old Access 2003 database that I'm having some trouble with. I'm trying to create some code that will allow users to toggle some specific linked tables between two Access database (essentially, they are switching between connecting these tables to current data and archived data). I've created the relatively simple code below that toggles the links for a specific list of tables between the two databases, which works fine for me. But when I try it with regular user permissions, I get an error at the RefreshLink stage that says "Could not create; no modify design permission for table or query 'TMP%#MAU@'" Below is the code:

Public Sub SwitchArchive()
Dim db As Database
Set db = CurrentDb
Dim ConnectString As String
Dim ArchTblName As Variant
Dim ArchTblList(0 To 8) As String

If db.TableDefs("TraneCommJobs").Connect = ";DATABASE=\\alt-tv-file1\Dbase\GATRANE\GeneralData.mdb" Then
    ConnectString = ";DATABASE=\\alt-tv-file1\Dbase\GATRANE\Archive.mdb"
Else
    ConnectString = ";DATABASE=\\alt-tv-file1\Dbase\GATRANE\GeneralData.mdb"
End If

ArchTblList(0) = "TraneCommJobs"
ArchTblList(1) = "SO_Numbers"
ArchTblList(2) = "SO_Portions"
ArchTblList(3) = "SO_Lines"
ArchTblList(4) = "Serials"
ArchTblList(5) = "CreditJobShipAddr"
ArchTblList(6) = "AncLines"
ArchTblList(7) = "OrderStatusLetters"
ArchTblList(8) = "TraneCommJobSplits"

For Each ArchTblName In ArchTblList

db.TableDefs(ArchTblName).Connect = ConnectString

db.TableDefs(ArchTblName).RefreshLink
    
Next
Set db = Nothing
End Sub

I don't have a table or query named "TMP%#MAU@". In the searching I've done, I've found other people hitting similar issues, it appears to be an internal table that is used when creating links. It clearly seems to be a permissions issue. I have tried temporarily giving the standard users full access (modify design and administer) on the related tables in both backend databases, and in the front end links. I've found some similar issues in some old threads searching online. I can't post links, but I found one reply that said:

These are the minimum permissions necessary for both the front end and the
back end when refreshing links:

Front end:

DbSecCreate
DbSecReadDef
DbSecRetrieveData

Back end:

DbSecDBOpen
DbSecRetrieveData

All of those seem to be basic open/read permissions that would be required for the database to even be usable, except for DbSecCreate.

And on another site:

Follow up:

After some intensive searching I discovered that my user 'owner' needs
dbSecCreate permission on his 'Table' Container to avoid this error. This
permission option doesn't show in the GUI when setting permissions. It took
some digging to find it in code, but all is good now.

As far as I can find, DbSecCreate can only be set via code, not via the GUI. So I've used the following code to set that permission to the pertinent group:

Sub Add_DBSecCreate()
   Dim dbs As Database, ctr As Container, strSystemDatabase
   strSystemDatabase = DBEngine.SystemDB
   Set dbs = DBEngine(0).OpenDatabase(strSystemDatabase)
   Set ctr = dbs.Containers!Databases  ' (I also tried dbs.Containers!Tables)
   ctr.UserName = "GeneralUser"
   ctr.Permissions = ctr.Permissions And dbSecCreate
End Sub

It compiles fine, it seems to run, but it doesn't fix the issue. I'm not even sure how to check to see if the permissions have been changed. If I add "debug.print ctr.Permissions" to my above code to edit permissions, it just returns 0, even after the "ctr.Permissions = ctr.Permissions And dbSecCreate" line, so maybe my code for adding those permissions is incorrect.

I'm really stumped here. Does anyone have any suggestions?

Thanks!

 

[arnelgp]

Google J Street Tech relinker

 

[dscline]

Thanks for the reply. Maybe I'm overlooking something, but I don't see any code there that addresses the permissions issue. If I use their code, just like mine, it works when logged in under my administrator user, but not if logged in as one of my other users. The issue I'm having is giving users in my "generaluser" group permissions to relink tables.

 

[dscline]

One small update: I seemed to have a bad reference to the proper syntax for adding permissions to existing permissions. My line:

ctr.Permissions = ctr.Permissions And dbSecCreate​

Should have used an OR instead of an AND:

ctr.Permissions = ctr.Permissions Or dbSecCreate​

After making that change, doing a debug.print of permissions now returns a 1 instead of 0, which is a change. However, after using the change to attempt to add dbSecCreate permissions to both the Databases and Tables containers hasn't helped. Users still don't have permission to refresh table links. So either my code to add dbSecCreate is still not quite right, or that's not the problem. Or at least not the only problem.

 

[arnelgp]

I may be wrong but permissions and security no longer exists starting a2007. You have to explicitly program same function through forms an vba.

 

[dscline]

Right, but I'm in a2003


With that said, I've fixed it. I'm an idiot. If I had really been paying attention to the code and thought about what it was doing, I would have seen this earlier. When I first hit the issue of non-administrative users not being able to refresh table links, I did some searching and found the posts stating that they need to be given DbSecCreate on the front end. So went off trying to find out how to do that, and I found some similar code to add DbSecDBCreate. So I simply modified that for DbSecCreate permissions instead. But the problem was, DbSecDBCreate needs to be applied to the workgroup file, so all this time I've been trying to add DbSecCreate to the workgroup DB, not my FE DB. Once I corrected that, I was able to add the appropriate permissions to my FE, and my refreshlink code now works.

So, just in case anyone else out there is still working in a really old version of access, and runs into this issue, the correct code I used to add DbSecCreate to my front end is:

Sub Add_DBSecCreate()
   Dim dbs As Database, ctr As Container
   Set dbs = CurrentDb()
   Set ctr = dbs.Containers!Tables
   ctr.UserName = "[I]user or group name[/I]"
   ctr.Permissions = ctr.Permissions Or dbSecCreate
End Sub