Computer Learning Zone CLZ Access Excel Word Windows

I don't think the human race will survive the next thousand years, unless we spread into space.

-Stephen Hawking
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Access >
Back to TechHelp    Comments List
Default FormsModulesMacros Upload Images   Link   Email  
Sandra Truax         
12 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
    ' 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       
12 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       
12 months ago

Sandra Truax         
12 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 in TechHelp.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/17/2024 12:49:29 PM. PLT: 0s