How to Use vbScript to Relink Your User's MS Access Application Front-End Automatically

[@seanmackenziedataengineering]

Make sure to check out the follow up for this video that puts this into a table of links in your database: kzbin.info/www/bejne/oXusi2CYbs6XqMk

[@seanmackenziedataengineering]

How many of you have struggled with keeping table links up-to-date? Not always easy!!

[@ristantosky]

in vba access, How to close front end when error popped up (suddenly disconnect because front end sleep)? I meant how to do "docmd.close" when pop up showed up. Or maybe what event name to do next action when connection interupted/disconnected ?

[@seanmackenziedataengineering]

That's a tough one! Usually a system error will stop everything else from running, so anything with a timer may be halted as well, meaning that it is hard to makes a procedure that checks for it. It is more likely that a preventative solution will work. If you can see how long a user has been idle for and know the sleep settings on their computer, you could program a check to see how long the user was idle. Use DoCmd.Quit just before their sleep threshold or something. Good one! This could be a neat video topic.

[@ristantosky]

@@seanmackenziedataengineering yeah, big question for me to know how to "catch" this error thing (disconnected BE) for vba access

[@mikewashington4188]

Actually, that's a wonderful suggestion of having the names of each table to be relinked in a helper hidden table for example relnk_tbls. The code would loop through the recordset of the helper table relinking each backend table using VBA code. Thanks a bunch

[@seanmackenziedataengineering]

Maybe I should do another video to show that. I had several people ask about it! Thanks

[@mikewashington4188]

@@seanmackenziedataengineering Thanks 🙏🏿. That would be wonderful.

[@akokohjoel9854]

How can one do the same to, linking ms access to MySQL with vbscript

[@seanmackenziedataengineering]

You don't need to use vbScript to connect to MySQL, you can just download and install the MySQL ODBC Driver > Create an ODBC DSN in Windows for MySQL > Create Linked tables to MySQL > use tables like you use Access tables.

[@gerfer6261]

👍👍👍👍👍 I have a vb script file to launch db to all users [ mining from google and put it together] I do I prevent users from editing vb script file please? Or any other substitute?

[@seanmackenziedataengineering]

You can set permissions on this file so that Users only have read/execute permissions and cannot edit the file. Or, you can put the file in another central location which cannot be changed and then put a .cmd file in All Users to call it, or call the script using Windows Scheduler so the user doesn't know where it is at all.

[@pitertoc448]

Thanks, keep the good work up

[@seanmackenziedataengineering]

Thanks, will do!

[@dbdata9836]

can up plz?

[@seanmackenziedataengineering]

Here you go: billzone.com/ep_148_vbscript_relink.zip

[@bumpersmith]

Thanks for the video Could you use strFE = CurrentProject.Path & "\" & MyFE.accdb and strBE = CurrentProject.Path & "\" & MyBE.accdb in the script instead if hard coding the path? I know that you are just trying to make quick videos but it would have been nice to see the table names listed in a table in the front end as you mentioned in the video which would make the script much more practical. Most Databases have many tables which would make this process very tedious.

[@seanmackenziedataengineering]

Thanks for the feedback! You can use the current directory like this: strDir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName) strFE = strDir & "\MyFE.accdb" strBE = strDir & "\MyBE.accdb"

[@bumpersmith]

@@seanmackenziedataengineering Thank You.

[@iqdevelopmet7809]

great job keep up the good job

[@seanmackenziedataengineering]

Thanks, will do!

[@seanmackenziedataengineering]

Download script at billzone.com/ep_148_vbscript_relink.zip

[@Djblois1]

The Link seems to be dead now

[@seanmackenziedataengineering]

@@Djblois1 Thanks for the heads up - link fixed.