Automatically relink backend tables

[SierraJuliet]

Anyone have a good method for relinking backend tables without organic MS Access 2013 ODBC utility? The reason I ask is because the frontend will be locked down and the EXTERNAL DATA > ODBC Database option will be unavailable.



The goal is to have a frontend and backend. The frontend is MS Access that opens a splash screen (check backend table links) and login window (accesses credential table). The backend tables are stored in MySQL.

[June7]

Review http://www.utteraccess.com/forum/lof...p/t678025.html

[SierraJuliet]

Thanks June7 but I prefer a working solution.

Someone has to have working VBA code for executing some sort of ODBC type connection from MS Access to relink tables in MySQL backend when the frontend is locked down. Based on what I have discovered, MS Access frontend and MySQL backend is a common trend and thus someone has came up with a solution to this problem. This is obviously not a concern when the frontend is not locked down; however, I doubt most frontend instances are not locked down if for nothing more than for end-product cleanliness.

[June7]

Some of the posts in that thread indicate a solution for relinking. None apply to your situation?

[Minty]

In addition to the link June provided, this is the defacto description of methods of using DSN Less connections.

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

it's written for SQL Server but simply needs the connection strings adjusting accordingly.

[isladogs]

Have a look at the outline of my approach which I've just written at another forum: https://www.access-programmers.co.uk...90&postcount=2

If you decide to use DSN less connections (which I recommend), also look at https://www.connectionstrings.com/
That site covers every possible connection you are ever likely to need (and lots you won't ...)

[Gicu]

Here is what I use to switch a front-end between two servers (development and production). I have a local settings table with a boolean field (VerifyLinks) to flag that the front-end needs to be re-linked. I call the vcVerifyMySQLLinks function on the very first line of my AutoExec macro (using RunCode). So when I need to force all front-ends to relink (i.e. for update purposes) I would check the VerifyLinks field, set the connection string in my ConnectionString field (these are both in a local hidden SettingsTable).

Public Function vcVerifyMySQLLinks()
Dim boVerify As Boolean
On Error Resume Next
boVerify = DLookup("[VerifyLinks]", "[SettingsTable]")
'refresh links and reset flag
If boVerify = True Then
    vcLinkTableDefs
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [SettingsTable] SET [SettingsTable].[VerifyLinks] = False"
    DoCmd.SetWarnings True
End If
End Function




Public Sub vcLinkTableDefs()


Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewConnectionString As String


On Error Resume Next




  Set dbs = CurrentDb()


  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked)


  For Each tdf In dbs.TableDefs


    If tdf.Connect <> "" Then
    strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
        If tdf.Connect <> strNewConnectionString Then
            tdf.Connect = strNewConnectionString
            tdf.RefreshLink
        End If
    End If
  Next
 


End Sub

Please note that you cannot have a default bound form set as the opening form if using this approach. I open my "switchboard" in the same autoexec macro on the line following the one the checking for/re-linking.

For MySQL I use the following connection string (stored in the ConnectionString field using a password Input Mask):
ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=YourServer;DATABASE=YourDatabaseNam e;PORT=3306;UID=YourUser;PWD=YourPassword;Option=4 194314

Cheers,
Vlad

[SierraJuliet]

Some of the posts in that thread indicate a solution for relinking. None apply to your situation?

I read through it and the solution from 2005 requires tweaking and troubleshooting to get working. I have been away from working my database for a while and the less reworking the better at the moment. I think someone has a solution for the very instance I am discussing.