Click here to Skip to main content
14,969,727 members
Articles / Programming Languages / Visual Basic
Tip/Trick
Posted 7 May 2021

Tagged as

Stats

1.5K views
25 downloads
4 bookmarked

Add Access Project to Source Version Control (Git) Repository

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
7 May 2021CPOL4 min read
Helping to add VBA project build in Access to SVN
In this article, you will see how to export all VBA code such as modules and classes, and other components such as forms, queries, reports, macros, tables, etc. as text files and export them Git system not as binary files but as text file. This will help to track the changes made in the code in Git repository system.

Introduction

Adding VBA project build in Access to SVN such as Git repository and shared between group of developers is not an easy task and that's why I am writing this article for assist developer using VBA.

Background

Most projects build in VBA using Access as platform, developer thinks it is hard to share the project between group of developers and track the changes made. In the early days, this can be done in MS VSS as repository with Add-Ins library form Microsoft, even TFS has provided the same technology, but limitation of VSS/TFS and comparing with Git as version control system that provides extensive benefits for developers by sharing the project code and track the change that has been made between developers. This is why Git is a preferable repository of choices as well as it is free.

This article is to show how to export all VBA code such as modules and classes, also other components such as forms, queries, reports, macros, tables, etc. as text files and export them Git system not as binary files but as text file. This will help to track the changes made in the code in Git repository system.

Same thing when file needs to be uploaded back to the project from text file to original format such as binary file.

Using the Code

The code provided is built in VBA for Access, please create a new module and call it "mod_SVC" and copy the code below to the new module.

This module contains functions that will Export/Import all components in Access project to/from your selected Source Version Control repository folder that was created in Git system.

Before exporting any component, you need to run "ExportAllVBAProjectReferences()" method in Immediate window and this will collect all VBA references and save in local table to be exported too.

To save all components in Git repository folder, you need to run "ExportToGit()" method in Immediate window, this method will export all project components VBA and Access objects to folder where the Source Version Control "GiT" saved.

To create new Access project form components that were saved in Git repository system, you need to:

  1. Run "ImportFromGit" method in Immediate window, this will ask for SVC folder that has all components that need to build new project.
  2. Then run "AddAllProjectReferences" method in Immediate window first to add all VBA references that project needs and uses.

If you need to save a single object (module, report, etc. from project to Git, highlight the object in Access Navigation Pane then call method "SaveItToGit" in Immediate window

Note when you upload all components from get to new project file, you need to call "AddProjectReferences" method in Immediate window to add all reference libraries that project uses to VBA you will be ask to provide Git folder path.

To upload a single object from Git to project, call "UploadFromGit" method in Immediate window, you will be asked to select the file from Git folder to upload.

Also, after any upload, you need to compile the project to find if there is any conflict or missing component(s) or reference(s).

The main function does the export below but you can find whole code in the attachment file, unzip the file and move the module to your project to use.

Nots how to use the code:

1. Three objects need to be imported into youe project before export or when importing into an empty project (Modules get_SVC and utility AND Table USysProjectReferences).

2. Include references in your project (Microsoft Office 16.0 Access database engine Object Library and Microsoft Office 16.0 Object Library).

3. Before running ExportProjectReferences, all rows of table USysProjectReferences should be deleted.  Otherwise, existing rows (in use or not) are retained.  Perhaps this step could be included in the startup of ExportProjectReferences.

4. When running ImportAllFromGit, you end up getting two copies of get_SVC and utilities:  One from you initial import into the empty project, and one from the import from the Git repository.  You need to delete the extra copies.

VBScript
Public Sub ExportAllToGit()
Dim oItem As Object
Dim strItemName As String, strSVC_location
    On Error GoTo ErrorHandler    
    strSVC_location = BrowseForFolder()
    If strSVC_location = "" Then
        MsgBox "SVC Git location was not define or located", _
                vbInformation + vbOKOnly, "SVC-Get Process"
        Exit Sub
    End If
    strSVC_location = strSVC_location & "\"
    '-Export all Forms
    For Each oItem In Application.CurrentProject.AllForms
        strItemName = oItem.Name
        Application.SaveAsText acForm, strItemName, strSVC_location & strItemName & ".frm"
    Next     
    '-Export all Reports
    For Each oItem In Application.CurrentProject.AllReports
        strItemName = oItem.Name
        Application.SaveAsText acReport, strItemName, strSVC_location & strItemName & ".rep"
    Next     
    '-Export all Macros
    For Each oItem In Application.CurrentProject.AllMacros
        strItemName = oItem.Name
        Application.SaveAsText acMacro, strItemName, strSVC_location & strItemName & ".mcr"
    Next
    '-Export all Queries
    For Each oItem In Application.CurrentData.AllQueries
        strItemName = oItem.Name
        Application.SaveAsText acQuery, strItemName, strSVC_location & strItemName & ".qry"
    Next
    '-Export all Tables
    For Each oItem In Application.CurrentData.AllTables
        strItemName = oItem.Name
        If Left(strItemName, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, "", strItemName, _
            strSVC_location & strItemName & ".csv", True
        End If
    Next
    strItemName = ""
    '-Export all diagrams
    On Error Resume Next
    For Each oItem In Application.CurrentData.AllDatabaseDiagrams
         If strItemName <> "" Then
            strItemName = oItem.Name
            Application.SaveAsText acDiagram, strItemName, _
                        strSVC_location & strItemName & ".dig"
        End If
    Next    
     strItemName = ""
    '-Export all Storprocedures
    For Each oItem In Application.CurrentData.AllStoredProcedures
         If strItemName <> "" Then
            strItemName = oItem.Name
            Application.SaveAsText acStoredProcedure, _
                        strItemName, strSVC_location & strItemName & ".stp"
         End If
    Next    
     strItemName = ""
    On Error GoTo ErrorHandler
    '-Export all Modules and classes
    For Each oItem In Application.VBE.ActiveVBProject.VBComponents
        Select Case oItem.Type
            Case vbext_ct_ClassModule
                strItemName = oItem.Name & ".cls"
               
             Case vbext_ct_StdModule
                strItemName = oItem.Name & ".bas"
            
        End Select
        
        If strItemName <> "" Then
            Call oItem.Export(strSVC_location & strItemName)
        End If
        strItemName = ""
    Next
    MsgBox "Export all project components to SCV git successfully"
GetOut:
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    GoTo GetOut
    Resume
End Sub 

Points of Interest

Note: This article is not to show you how to install or use Git repository system, but how to add VBA Project to the system to be ready to submit as repository.

For those looking to VBA in Excel, there is another article about the same issue.

History

  • 7th May 2021: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

VBA55
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
QuestionNotes and suggestions Pin
Jim Rolph28-May-21 9:37
MemberJim Rolph28-May-21 9:37 
AnswerRe: Notes and suggestions Pin
VBA5522-Jun-21 10:35
MemberVBA5522-Jun-21 10:35 
QuestionThanks for this article, but the download is missing. Pin
Jim Rolph23-May-21 8:57
MemberJim Rolph23-May-21 8:57 
AnswerRe: Thanks for this article, but the download is missing. Pin
VBA5526-May-21 10:01
MemberVBA5526-May-21 10:01 
GeneralRe: Thanks for this article, but the download is missing. Pin
Jim Rolph26-May-21 21:22
MemberJim Rolph26-May-21 21:22 
GeneralRe: Thanks for this article, but the download is missing. Pin
VBA5528-May-21 4:16
MemberVBA5528-May-21 4:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.