Is there VBA code to edit connections for linked tables?

[ArviLaanemets]

I have a splitted app with MS SQL database as BE (Really this will be several such apps, identical in design, but every one linked to it's own SQL BE. Every such app will serve for single site of company. All BE DB's are similar in design, but get data from different ERP databases). The connection for linking tables is created used Connection File (e.g. MyConnection.dsn). The number of linked tables/views is ~30.



Currently, when I need to create a new app for another site, after I create a new SQL DB, I make a copy from existing app, and link all tables/view in new BE to it. As all old links were renamed, now I have both old and new links in FE. Now I rename every one of new linked tables like old ones, and all old links are replaced. Together with testing, relinking all those tables takes approximately a day.

Also, there may be a situation where the the address of resource where the Connection File is stored is changed, and I have to reconnect tables. Linked Table manager has Relink option, but this allows edit the contents of Link File, not it's address or name. So for this situation also I have to relink all tables manually, and then rename links!

So now my question! Is there a way to do all this using VBA (giving name and address of old Connection File and new Connection File as parameters)?

I did something similar in Excel. After we updated to MS Office 2016, queries to tables in same Excel file (I used fixed Named Ranges as sources) did run only after they were edited, and then for current instance only (maybe the reason was, we have 64-bit Windows, but 32-bit Office because our HQ has a lot of templates for 32-bit office without any access rights, and the firm creating them nonexistent anymore). So I created an OnOpen event for such files, which did read the queries datasources, and updated them. As an additional bonus, the event also checks the address of Excel file, and in case this was changed, it updates it accordingly. With Excel it was simple - even when you use Connection File to create a link, all connection info is copied into Excel Datasource, and the Connection File itself is moot after this.
With Access, I haven't a clue from where to start!

[Minty]

Yes but not using a connection file

Something like this?

Function fnRefreshTableLinks()
    
    Dim sConn As String
    
    
    sConn = "DRIVER=ODBC Driver 17 for SQL Server;SERVER=YourServer.detials.go.here;UID=A_User;PWD=Password;Trusted_Connection=No;APP=Microsoft Office;DATABASE=YourSqlDatabaseName;"
    
    
    Dim tdf As TableDef
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = sConn
            tdf.RefreshLink
        End If
    Next tdf


End Function

Obviously replace the connection string as per your server.
All you need to do now is copy the source DB then refresh all the links with the new connection string.

You can add code to point Pass through queries to the same location.

[ArviLaanemets]

Are you saying, Access simply writes connection info from Connection File into Connection String of Access app? Exactly same as Excel! So after I link tables using Connection File, I can delete the Connection File and all will work???!!!

[Minty]

Basically yes!

If you SELECT * FROM Msysobjects

You will see the connection strings as they are stored.
Caution - don't mess with that table - there lies the path to corruption.

[NoellaG]

Hi Minty, I would kike to ask some extra info here: the policy of my main customer is to track all Office (Excel/Access) applications that try to connect our SQL servers and delete them from the network/laptop drive. The user and his/her manager get a warning message. Reason for this is that it is considered very unsafe. Actually, other customers do use Access front-ends to SQL server. When you say that you can see the connection string in the Access system tables, do you mean you can see the user name and password? If that would be true, there is a real security problem.

[Minty]

You certainly can if you know how to look.
It only stores the password if you aren't using a trusted connection, which in a self hosted environment you would be normally, in which case the SQL Server would authenticate against your AD login.

As usual there are a lot of possibilities for connecting.
If you only connect the tables after a the DB opens, and disconnect on close, and only distribute a locked down accde then the security issue becomes a lot less of a headache.

Colin has some excellent advice on securing a front end here http://www.mendipdatasystems.co.uk/i...ity/4594461803
And his security challenges put into practice some of the techniques.

[ArviLaanemets]

E.g. in our network, for every SQL database is it's own domain group (or a couple of them), with determined access rights. Access to SQL DB have only members of such group(s). And of-course they must be domain members too!