TechHelp

Default FormsModulesMacros Upload Images Link
Sandra Truax
3 months ago

I have a database called Global that I enter all the wonderful code that I have been compiling from Richard's videos. Until today, it has been a pain to update all my individual databases with the new updated codes and main form.  Thanks to GPT, now all I have to do is copy one module "ResetGlobal" run the code "ResetGlobalF" in the immediate window, and it will replace the two files with the updated info, plus check to make sure all the other forms, macros, modules, exist, and if not copy them to the other database also.    Here is the code that, thanks to ChatGPT, I use.  Thought someone else might find this handy.

Sub ResetGlobalF()
    Dim externalDB As Object
    Dim currentDB As DAO.Database
    Dim externalPath As String
    Dim externalDBName As String

    ' Delete current files
    If ObjectExists("GlobalF", acForm) Then
        DoCmd.DeleteObject acForm, "GlobalF"
    End If

    If ObjectExists("MyGlobalMod", acModule) Then
        DoCmd.DeleteObject acModule, "MyGlobalMod"
    End If

    ' Set the path and name of the external database
    externalPath = "d:\access\"
    externalDBName = "global.accdb"

    ' Open the external database
    Set externalDB = Application.DBEngine.OpenDatabase(externalPath & externalDBName)

    ' Set the current database
    Set currentDB = currentDB

    ' Check if the form "GlobalF" exists in the current database
    If Not ObjectExists("GlobalF", acForm) Then
        ' Copy the form
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acForm, "GlobalF", "GlobalF"
    End If

    ' Check if the module "MyGlobalMod" exists in the current database
    If Not ObjectExists("MyGlobalMod", acModule) Then
        ' Copy the module
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acModule, "MyGlobalMod", "MyGlobalMod"
    End If

    ' Check if the macro "MyMacro" exists in the current database
    If Not ObjectExists("MyMacro", acMacro) Then
        ' Copy the macro
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acMacro, "MyMacro", "MyMacro"
    End If

    If Not ObjectExists("AutoExec", acMacro) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acMacro, "AutoExec", "AutoExec"
    End If

    If Not ObjectExists("SetVariables", acModule) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acModule, "SetVariables", "SetVariables"
    End If

    If Not ObjectExists("MyTempVars", acForm) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", externalPath & externalDBName, acForm, "MyTempVars", "MyTempVars"
    End If

    ' Close the external database
    externalDB.Close

    ' Clean up the objects
    Set externalDB = Nothing
    Set currentDB = Nothing

    MsgBox "Objects copied successfully!", vbInformation
End Sub

Function ObjectExists(objName As String, objType As AcObjectType) As Boolean
    Dim obj As AccessObject

    On Error Resume Next
    Set obj = CurrentProject.AllForms(objName)
    If obj Is Nothing Then
        Set obj = CurrentProject.AllModules(objName)
    End If
    If obj Is Nothing Then
        Set obj = CurrentProject.AllMacros(objName)
    End If
    On Error GoTo 0

    ObjectExists = Not (obj Is Nothing)

    Set obj = Nothing
End Function

Kevin Yip
3 months ago

In the ObjectExists() function, the objType argument is not used. So even if you specify what type of object to transfer, it won't be taken into account. In the rare instance of objects sharing the same name, which Access unfortunately allows to happen (see picture below), your ObjectExists() function may not know the right object type to use, thus returning incorrect result. I've seen many times when ChatGPT let little things like this slip by.

Kevin Yip
3 months ago

Sandra Truax
3 months ago

Thank you for this information, Kevin. I will be sure to use unique names so that this doesn't happen.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to TechHelp Comments

Subscribe to TechHelp
Get notifications when this page is updated