Automatically re-linking tables

[mcelligott]

This is a re-post from an earlier posting. Unfortunately I could not get the suggestion I received to work.

I have the following code run when the front-end of my database opens. It re-links the tables if it finds the primary server (there is more that determines if it does not see the server to re-connect to the local machine).

Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Set firsttbl = Dbs.TableDefs("Alarm Companies")

'Determines if the table Alarm Companies is already connected to the ECD Server
If firsttbl.Connect = ";Database=" & "\\ps911pdc\Operations Database\ECD Operations Database_be.mdb" Then
GoTo lastline
End If

'Loop through the tables collection and relinks to the ECD server if not already connected
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then
Tdf.Connect = ";Database=" & "\\ps911pdc\Operations Database\ECD Operations Database_be.mdb"
Tdf.RefreshLink
End If
Next

lastline:

It works great when I am the user signed on but when the other users (non-admin) sign on, they get the following error:

Run-time error '3033':

You do not have the necessary permissions to use the 'Alarm Companies' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.

What I think it is saying is that if the user does not have the ability to modify a table design then they can not re-link to another source. I definitely do not want the users able to modify table design but unfortunately do want them able to re-link. Is there a way to put in the code to use an administrator's username and password just for the re-linking purposes. Since the regular user does not have the ability to see the background code, them getting ahold of the password for that account should not be an issue.

Thanks,

Bob

 

[JerryKlmns]

mcelligott
When you use CurrentDb (DAO) or CurrentProject.Connection (ADO) they both have the CurrentUser 's permissions. You 'll have to open a brand new database (DAO) or connection (ADO) object, using your UserID & Password to do the job

or

assign Read Data permissions to your source tables and full permissions to the destination tables for each Group or User in the front-end database. It is recommended to grant full permissions on <New Tables> because this has no effect on the source tables, thus inheritting full permissions for relinked tables. (According to the link that Remou provided in thread700-1324254, last year!)

BTW, I would use a recordset to loop all linked tables and not the collection of all tables! Check out MSysObjects (hidden) table

 

[mcelligott]

Jerry,

That worked PERFECTLY in every simulation I could think of and did not affect the security. I THANK YOU THANK YOU THANK YOU!! I have been pounding my head against the computer for over a year trying to figure this out. I am now able to provide full redundancy.



Bob

 

[JerryKlmns]

Well, I 'm glad to be of help for a big pain on the ... security for over a year.

You 're welcomed.