In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
'This function loop through all linked tables in MS Access database
'and re-link them to new database path
Public Function UpdateLinkTables()
'
Dim strPath As String
Dim objDatabase As Object
Dim tblDef As TableDef
'
strPath = "E:\Work\ExcelSirJi\Database.accdb"
'
'Set the object to current database
Set objDatabase = CurrentDb
'
'Go through each table in the database and check if it is linked table
'if yes, then update new database path
For Each tblDef In objDatabase.TableDefs
If tblDef.SourceTableName <> "" Then
'If it is hidden table then make it a visible table
tblDef.Properties("Attributes").Value = 0
'Change the database path to new path
'if database requires password then you can un-comment password section in below code
tblDef.Connect = ";DATABASE=" & strPath '& "; PWD=1234"
'Refresh the table
tblDef.RefreshLink
End If
Next
'
'Close the objects
Set tblDef = Nothing
Set objDatabase = Nothing
'
End Function
5. In the above code I have hardcoded new database path in strPath variable but you can make this variable as input parameter of the function by making below changes highlighted in the screenshot (Red are deletion, Yellow are addition):
Hope you liked this article !!
Subscribe our blog for new amazing excel tricks.
Click to below for some more interesting tricks and learning:
Please leave your valuable comments in Comments section:
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
VBA Code To Add New Sheet In VBA, it is sometime important to add a worksheet at the right place in the Excel. Here is a simple an effective code that adds a new worksheet…
VBA Code to list Files in Folder To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder. Here is a simple code for you,…
VBA Code to Convert MM.DD.YYYY To DD.MMM.YYYY in Excel In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a…
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.
VBA Code To Change Cell Color Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this, you can use…