Link and Relink Your Access Front-End App Using vbScript and a Table-Based Approach
HTML-код
- Опубликовано: 22 фев 2022
- In this episode, we look at our vbScript relinking process and convert it to use a table-based approach, so that we can create a table with all of our desired table links, instead of hard-coding those values in. This approach is better suited to applications that have many table links in them that need to be refreshed. We'll go into our front-end file, create a table to house our desired links, then change the script we made in our last episode to use that table to create table links.
Related Videos:
How to disable and enable the Bypass Key on your MS Access app so users cannot bypass Autoexec/Forms
• How to disable and ena...
Link and Relink Your Access Front-End App Using vbScript and a Table-Based Approach
You are watching this video now!
How to Use vbScript to Relink Your User's MS Access Application Front-End Automatically
• How to Use vbScript to...
Use vbScript to Deploy your Access Front End, Create Shortcuts, and Check for Updates on User Login
• Use vbScript to Deploy...
How to Encrypt and Hash Passwords in MS Access
• How to Encrypt and Has...
How to Encrypt Sensitive Data in Microsoft Access
• How to Encrypt Sensiti...
How to Kick Users out of Your MS Access Database
• How to Kick Users out ...
How to Get the IP Address in Microsoft Access
• How to Get the IP Addr...
How to Get the Windows Username in MS Access
• How to Get the Windows...
How to Set Basic Startup Options on Your Microsoft Access Database
• How to Set Basic Start...
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#access #vbscript #database
• Link and Relink Your A... 
Наука
Thanks to everyone who requested this topic! It is a useful one. How many tables are YOU linking in your database today? Please comment below!
Good! Very Good!
Thank you! Cheers!
DAYUM SON! This content is smokin!!! This is exactly what I wanted to learn!!!
Bravo
Thanks!
This was EXACTLY the information I was looking for. I applaud creating separate functions to handle the linking and deleting of tables as this can only lead to cleaner code. For those who want to see the details of the LinkMyTable function (I couldn't see it in this video), they can be seen in another video that I had trouble finding at ruclips.net/video/JhYgRjkAEnA/видео.html named "How to Use vbScript to Relink Your User's MS Access Application Front-End Automatically"
Hey thanks for the feedback and heads up on the previous video. I'm working on making these "easy to read" :-) cheers!
Thank you. This is a much better approach.
To expand on this, can the backend folder path be set ahead of time with a Tempvar which could be filled from a field in the user's setting table?
When distributing the application, the backend path would likely be different on everyone's system.
As for your question above about how many tables I am linking, the current application I am developing so far is about 30.
This scenario is a good example of how to use this deployment. I created the BackendDB field in tblRelinkTables, and that table is local to the front-end. This means you have the option for a custom deployment each time. If user A is on network 1 and user B is on network 2 and they have a slightly different UNC for the network share with the backend, you can just deploy a front-end with their localized "view" of the network. A might see \\server1\share\MyBE.accdb and B might see \\server2\someotherpath\access\MyBE.accdb. Either is fine, you just need to load their "profile" at the time of deployment. Then, when table links are updated and need to be refreshed for some reason, this script will do it seamlessly, with no changes to your front-end code. Or, create some code to load their FE tblRelinkTables links just before the file is deployed to their computer.
30 tables! Definitely worth it to use a table!
Will I be able to use this same VBA script within the Access database as a module?
Yes you can! I demonstrate moving vbScript to a VBA module at the end of this video: ruclips.net/video/7n80z9WkTcc/видео.html
Pls how can one use vbscript to Link ms access to MySQL
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.
Pls how can we get the vbscript, kindly give us Link
Here you go!
Dim app, wks, db, strBE, strFE, rst, strTable, strAlias
strFE = "C:\DEV\TestApp\MyFE.accdb"
'strBE = "C:\DEV\TestApp\MyBE.accdb"
Set app = CreateObject("Access.Application")
Set wks = app.DBEngine.CreateWorkspace("", "admin", "", 2) 'dbUseJet
Set db = wks.OpenDatabase(strFE)
Set rst = db.OpenRecordset("tblRelinkTables")
Do Until rst.EOF
strBE = rst("BackendDB") 'multiple backends possible
strTable = rst("TableName")
strAlias = rst("AliasName")
LinkMyTable db, strBE, strTable, strAlias
rst.MoveNext
Loop
rst.Close
db.Close
app.Quit
MsgBox "Your application was updated.", vbInformation, "Update"
'Cleanup
Set db = Nothing
Set wks = Nothing
Set app = Nothing
Sub DeleteOldTable(db, strTable)
'Deletes an old table def
On Error Resume Next
db.TableDefs.Delete strTable
End Sub
Sub LinkMyTable(db, strBE, strSource, strAlias)
'Links a table to a backend file
Dim strConnect
Dim tdf
DeleteOldTable db, strAlias
strConnect = ";DATABASE=" & strBE
Set tdf = db.CreateTableDef(strAlias,0,strSource,strConnect)
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
Unfortunately the Vbscript is unable to relink my FE to my various BEs that are password protected. This is because the Script deletes the old linked table which is linked through a password and creates a new table which brings an "Invalid Password error" vbscript pop up message. Is there a workaround to this problem?
In the LinkMyTable sub, you can change the strConnect line to:
strConnect = ";DATABASE=" & strBE & ";UID=MY_USERNAME;PWD=ABC123;"
If it is a simple password protect, you can try leaving out the UID parameter.
@@seanmackenziedataengineering Your suggestion worked thank you very much. Ever since i stumbled onto your youtube channel i watch them alot especially the videos with MS Access, keep up the good work i love them👍