miraclez
01-11-2019, 05:10 PM
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/help/291264/how-to-refresh-links-in-a-runtime-application-in-microsoft-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 :crying::crying:
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/help/291264/how-to-refresh-links-in-a-runtime-application-in-microsoft-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 :crying::crying: