Split Database in MS Access

 

What is a Split Database in MS Access?

A split database in MS Access is a database architecture where the database is divided into two separate files:

1. Front-End (FE) Database – Contains forms, reports, queries, macros, and VBA code (UI).
2. Back-End (BE) Database – Contains tables and data (stored separately).

The Front-End connects to the Back-End using linked tables.

---

Why Split a Database?

Splitting an Access database provides several benefits:
✅ Improves Performance – Reduces network traffic when multiple users access the database.
✅ Supports Multi-User Access – Many users can work on the database without conflicts.
✅ Enhances Security – The back-end file can be placed on a secure server while users access the front-end.
✅ Simplifies Updates – You can modify the front-end (UI) without affecting the data.
✅ Reduces Corruption Risk – Less risk of database corruption due to file locking conflicts.

---

How to Split an Access Database?

Step 1: Use the Database Splitter Tool

1. Open your Access Database (.accdb).
2. Click on Database Tools > Access Database (Split Database).
3. Follow the wizard to split the database.
4. Choose a location to save the Back-End database.
5. After splitting, Access automatically links the front-end to the back-end.

Step 2: Distribute the Front-End to Users

• Copy the front-end (.accdb file) to each user's computer.
• The front-end links to the back-end via linked tables.

---

How to Link or Relink Tables to the Back-End?

If the back-end file moves, you need to relink tables:

1. Go to "External Data" > "Linked Table Manager".
2. Select all linked tables and click "Relink".
3. Browse to the new back-end location and confirm.

Alternatively, you can use VBA to relink tables dynamically:

vba
Sub RelinkTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strPath As String
    
    ' Path to the Back-End database
    strPath = "C:\Path\To\Backend.accdb"
    
    ' Open the Front-End database
    Set db = CurrentDb
    
    ' Loop through linked tables and update path
    For Each tdf In db.TableDefs
        If tdf.Connect <> "" Then  ' Check if it's a linked table
            tdf.Connect = ";DATABASE=" & strPath
            tdf.RefreshLink  ' Refresh the table connection
        End If
    Next tdf
    
    ' Cleanup
    Set tdf = Nothing
    Set db = Nothing
    
    MsgBox "Tables relinked successfully!", vbInformation
End Sub

---

When Should You Split a Database?

🔹 If multiple users need access.
🔹 If the database grows large (50MB+).
🔹 If you want to protect data integrity.
🔹 If you plan to migrate to SQL Server later.

---

Alternative: Using SQL Server as a Back-End

Instead of Access for the back-end, you can migrate tables to SQL Server using the SQL Server Migration Assistant (SSMA). This improves scalability and security.

Microsoft Dynamics CRM User License Types and their meaning

User License Type	License Type	Meaning
1	Professional	Full access user with comprehensive capabilities.
2	Administrative	User with administrative privileges for system management.
4	Basic	User with limited access, suitable for basic functionality needs.
5	Essential	User with minimal access, typically for lightweight tasks.
6	Device	License assigned per device, allowing multiple users to share the same device.
7	External Connector	Allows external users to access the system without individual licenses.
8	Limited	User with restricted access, confined to specific modules or features.
9	Read-Only	User permitted only to view data without making any modifications.
10	Team Member	User with access to collaborative features and basic functionalities.
11	Student	License tailored for educational purposes, providing necessary tools for students.
12	Faculty	License designed for educational staff, offering tools required for teaching.
13	Guest	Temporary access for external users, often with limited permissions.
14	Partner	Access provided to business partners, enabling collaboration and specific functionalities.
15	Developer	User with access to development tools and environments for creating and testing applications.
16	Tester	User designated for testing purposes, with access to testing tools and environments.
18	Enterprise	Comprehensive access for users within an enterprise, covering all standard features.
21	Business	License suitable for business users, providing essential tools for business operations.
22	Non-Profit	License offered to non-profit organizations, often at a discounted rate.
23	Government	License tailored for governmental agencies, adhering to specific regulatory requirements.
24	Academic	General license for educational institutions, covering both students and faculty.
25	Trial	Temporary license allowing users to evaluate the system before committing to a purchase.
26	Subscription	License based on a recurring payment model, providing access as long as the subscription is active.
27	Perpetual	One-time purchase license granting permanent access to the software.
28	Concurrent	License model allowing a set number of users to access the system simultaneously.
29	Floating	Licenses that are not assigned to specific users but can be used by any user within a group, up to a limit.
30	Node-Locked	License tied to a specific device or node, restricting usage to that particular hardware.
32	Site	License permitting unlimited use within a particular location or site.
33	Global	License allowing usage across multiple locations worldwide.
34	OEM	Original Equipment Manufacturer license, typically bundled with hardware.
35	Volume	License purchased in bulk, often at a discounted rate, for large organizations.
42	Cloud Solution Provider	License provided through a cloud solution partner, offering cloud-based services and support.
43	Service Provider License Agreement	Licensing program for service providers, enabling them to offer software services to end customers.
44	Academic Volume	Volume licensing program specifically for educational institutions, facilitating large-scale deployments.
66	Classroom	License tailored for classroom settings, providing tools necessary for teaching and learning.
68	Developer Network	Subscription-based license providing developers with access to a wide range of Microsoft software for development and testing.
70	Competency	License granted to partners who have demonstrated expertise in specific areas, providing additional benefits and support.
72	Reseller	License for organizations authorized to sell software products to end customers.
73	Distributor	License for entities responsible for distributing software products to resellers.

Power Automate - How to split a string and loop through the splitted string in Power Automate

    1. Click on New flow and Create an Instant Cloud Flow.

        2. Enter the Flow Name, select Manually Trigger a Flow, Click on Create.

        3. Click on New Step.

    

        4. Type Compose in search box and click Compose action from Data Operation under All tab.

        5. Input some countires and seperate it with ";".

         6. Click on New Step, type initialize variable in search box and click Initialize Variable from Actions Tab.

    

        7. Select variable type as Array, Give a name to Array Variable "Split Array".

            Write an expression to split a string from compose output.

            For that, click on Add dynamic content -> Expression

            Under Expression input box, write a function Split(outputs('Compose'),';')

            Click Ok.

        8. Click on New Step, type Apply to each in search box and click Apply to each control from Actions Tab.

        9. In Apply to each action, insert Split Array from dynamic content.

        10. Under Apply to each action create a Compose action and manually input "@item()" to get splitted values.

        11. To see output, Save and Test flow and Run flow manually.

    

        12. To see iterated values, click on Next.

VBA Advanced Topics

Class Modules

VSTO Add-Ins

Creating Add-Ins using Custom UI Editor

Microsoft Edge and Google Chrome automation using Selenium IDE

Adobe Acrobat Pro Automation

Invoke a SOAP Web Service

Update Share point list from Excel and extract data from share point to excel

SQL Connection

Pivot Table

Multiple dependent hierarchy with Index and Match

Mail Merging

Office Add-Ins

Error Handling Techniques

Dictionary and Collection

Arrays

Ubound and Lbound

API Functions

Shell Scripting(WScript)

How to find recent excel file in a folder.

Sub Recent_File()

Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'specify the directory
Directory = "C:\Users\" & Environ("username") & "\Downloads\"
FileName = Dir(Directory & "*.xlsx", 0)
If FileName <> "" Then
    MostRecentFile = FileName
    MostRecentDate = FileDateTime(Directory & FileName)
    Do While FileName <> ""
        If FileDateTime(Directory & FileName) > MostRecentDate Then
             MostRecentFile = FileName
             MostRecentDate = FileDateTime(Directory & FileName)
         End If
         FileName = Dir
    Loop
End If
NewestFile = MostRecentFile
MsgBox "Recent File is " & NewestFile
End Sub

How to copy sheets from one file to another using VBA code

Sub Consolidation_Sheets()
Dim Sheets_Count As Integer
Dim fso As Object
Dim selectedpath As String
Dim strpath As Object
Set fso = CreateObject("scripting.filesystemobject")
fso.createfolder ThisWorkbook.Path & "\Destination"
Workbooks.Add
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Destination\OutputFile.xlsx"
    With Application.FileDialog(msoFileDialogFolderPicker)
                    .AllowMultiSelect = False
                    .InitialFileName = "Please select Files Folder"
                    .Title = "Please select Files Folder"
            MsgBox "Please select Folder"
            If .Show = True Then
                selectedpath = .SelectedItems(1) & "\"
            Else
                MsgBox "User has cancelled"
                Exit Sub
            End If
    End With
   
    For Each strpath In fso.getfolder(selectedpath).Files
        Workbooks.Open strpath
        Z = ActiveWorkbook.Sheets.Count
        y = 0
        For Sheets_Count = ActiveWorkbook.Sheets.Count To 1 Step -1
            x = Workbooks("OutputFile.xlsx").Sheets.Count
            y = y + 1
            ActiveWorkbook.Sheets(Sheets_Count).Copy after:=Workbooks("OutputFile.xlsx").Sheets(x)
            Workbooks("OutputFile.xlsx").Save
            If y = 2 Then
                strpath1 = Right(strpath, Len(strpath) - Len(selectedpath))
                Workbooks(strpath1).Close
                Exit For
            End If
        Next Sheets_Count
        If Z = 1 Then
            strpath1 = Right(strpath, Len(strpath) - Len(selectedpath))
            Workbooks(strpath1).Close
        End If
    Next strpath
End Sub

--------------------------------------------------------------------------------------------------------------------------

Copying sheets from one file to multiple files

Sub Consolidation_Sheets()

Dim Sheets_Count As Integer
Dim fso As Object
Dim selectedpath As String
Dim strpath As Object

Application.DisplayAlerts = False

Set fso = CreateObject("scripting.filesystemobject")

    With Application.FileDialog(msoFileDialogFolderPicker)
                    .AllowMultiSelect = False
                    .InitialFileName = "Please select Files Folder"
                    .Title = "Please select Files Folder"
            MsgBox "Please select Folder"
            If .Show = True Then
                selectedpath = .SelectedItems(1) & "\"
            Else
                MsgBox "User has cancelled"
                Exit Sub
            End If
    End With
   
    For Each strpath In fso.getfolder(selectedpath).Files
        If Right(strpath, Len(strpath) - Len(selectedpath)) = "Testing.xlsx" Then
            Workbooks.Open strpath
            Exit For
        End If
    Next strpath
   
    For Each strpath In fso.getfolder(selectedpath).Files
        If Right(strpath, Len(strpath) - Len(selectedpath)) <> "Testing.xlsx" Or Right(strpath, Len(strpath) - Len(selectedpath)) <> "~$Testing.xlsx" Then
            Workbooks.Open strpath
            strpath1 = Right(strpath, Len(strpath) - Len(selectedpath))
            Workbooks("Testing.xlsx").Sheets(Workbooks("Testing.xlsx").Sheets.Count).Copy after:=Workbooks(strpath1).Sheets(Workbooks(strpath1).Sheets.Count)
            Workbooks("Testing.xlsx").Sheets(Workbooks("Testing.xlsx").Sheets.Count - 1).Copy after:=Workbooks(strpath1).Sheets(Workbooks(strpath1).Sheets.Count)
            Workbooks(strpath1).Save
            Workbooks(strpath1).Close
        End If
    Next strpath
    Workbooks("Testing.xlsx").Close

End Sub
 


 

How to automate Calculator using VBScript

Set objshell = wscript.CreateObject("wscript.shell")
objshell.Run "calc.exe"
objshell.AppActivate "Calculator"
wscript.sleep 2000
objshell.SendKeys "23*23="
msgbox "completed"

Develop this code in notepad and save it as .vbs