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
    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       
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.
 

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

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