Changing the Table Links of a DB by connecting from another DB using VBA

[miraclez]

So I got into a new job and one of my tasks is to upgrade their existing years-old Access application to work with SQL Server on multi-user mode over the Intranet. While I'm not a programmer, I'm the solo supposed-to-know-it-all I.T. guy they might reach out even if the Coffee machine doesn't work. The front end of the Access 2007 program has been converted to MDE, and no one has any clue as to where the MDB would be. The backend seems to be a split of the front end, and it's still on it's MDB extension.

I've managed to successfully migrate the Backend over to SQL Server Express 2017, but I'm unable to make modifications to the links on the Front end. Looking around, I found solutions where even Microsoft has posted a similar solution using VBA. Here's the link, https://support.microsoft.com/en-us/...crosoft-access to a similar solution.

Also, I found this code which might relate to my problem, however I've sat 2 whole days trying to study how it works, and have reached a dead end. So here I am, trying to reach out to you guys if you can help me out. Here's the other code I found online.

Const LnkDataBase ="C:\MyDB_be.accdb"
Const DBPassword ="123"

Sub relinktables()
'Routine to relink the tables automatically. Change the constant LnkDataBase to the desired one and run the sub
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable AsString
Set dbs = CurrentDb()
ForEach tdf In dbs.TableDefs
    If Len(tdf.Connect)>1Then'Only relink linked tables
        If tdf.Connect <>";DATABASE="& LnkDataBase Then'only relink tables if the are not linked right
            If Left(tdf.Connect,4)<>"ODBC"Then'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect ="MS Access;PWD="& DBPassword &";DATABASE="& LnkDataBase
                dbs.TableDefs(strTable).RefreshLink
            EndIf
        EndIf
    EndIf
Next tdf
EndSub

PS: I'm very disappointed that my Country (United Arab Emirates) is not listed in the drop-box of the registration form

[ikanni]

Hi Miraclez,
As I understand you cannot to do anythink. The MDE app isn't editable. You need find the original mdb source to re link new tables Sql server. Or make new one
Regards

[miraclez]

Hi Miraclez,
As I understand you cannot to do anythink. The MDE app isn't editable. You need find the original mdb source to re link new tables Sql server. Or make new one
Regards

Thank you for your reply ikanni..

I was able to make basic changes to the Tables while running the file through SSMA. Also, the VBA I posted above seems capable of getting the job done.