Results 1 to 8 of 8
  1. #1
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87

    Needing advice on database.execute method


    I have a backup routine

    Code:
        Dim FSO As New FileSystemObject
        Dim FilePath As String, FileDest As String
        Dim FolderName As String, ExtHD As String
        
        With DoCmd
            .SetWarnings False
            .OpenQuery "CustomerTBackupQ"
            .OpenQuery "DonorTBackupQ"
            .OpenQuery "EmployeeTBackupQ"
            .OpenQuery "LabTBackupQ"
            .OpenQuery "MROTBackupQ"
            .OpenQuery "OfficerEmployerTBackupQ"
            .OpenQuery "ReasonForTestTBackupQ"
            .OpenQuery "TestTBackupQ"
            .OpenQuery "GroupTBackupQ"
            .OpenQuery "SettingTBackupQ"
            .OpenQuery "InventoryTBackupQ"
            .OpenQuery "InventoryXOperationBackupQ"
            .OpenQuery "ConsortiumTBackupQ"
            .OpenQuery "DonorSignatureTBackupQ"
            .OpenQuery "CollectorSignatureTBackupQ"
            .OpenQuery "SignInTBackupQ"
            .OpenQuery "DistrictTBackupQ"
            .SetWarnings True
        End With
        
        FolderName = Format(Date, "mmmmyy")
        FilePath = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\BackupsFromQuery_aavdb.accdb"
        FileDest = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\" & FolderName & "\be_backup_" & Format(Date, "mm-dd-yyyy") & ".accdb"
        ExtHD = "D:\Database Backups\" & FolderName & "\be_backup_" & Format(Date, "mm-dd-yyyy") & ".accdb"
        
        FSO.CopyFile FilePath, FileDest, True
        On Error Resume Next
        FSO.CopyFile FilePath, ExtHD, True
        MsgBox "Backup Successful!", vbOKOnly + vbExclamation
        
        Set FSO = Nothing
    but it was suggested to me to try the database.execute method like this:

    Code:
    With CurrentdB
           .Execute "CustomerTBackupQ", dbFailOnError
           .Execute "DonorTBackupQ", dbFailOnError
           .Execute "EmployeeTBackupQ", dbFailOnError
           .Execute "LabTBackupQ", dbFailOnError
           .Execute "MROTBackupQ", dbFailOnError
           .Execute "OfficerEmployerTBackupQ", dbFailOnError
           .Execute "ReasonForTestTBackupQ", dbFailOnError
           .Execute "TestTBackupQ", dbFailOnError
           .Execute "GroupTBackupQ", dbFailOnError
           .Execute "SettingTBackupQ", dbFailOnError
           .Execute "InventoryTBackupQ", dbFailOnError
           .Execute "InventoryXOperationBackupQ", dbFailOnError
           .Execute "ConsortiumTBackupQ", dbFailOnError
           .Execute "DonorSignatureTBackupQ", dbFailOnError
           .Execute "CollectorSignatureTBackupQ", dbFailOnError
           .Execute "SignInTBackupQ", dbFailOnError
           .Execute "DistrictTBackupQ", dbFailOnError
    End With
    When I run this method, it immediately returns an error saying it can't find database object CustomerTBackupQ, which is the first item on the list. Is there anyone who has some experience with this method. I'm sure it's a matter of faulty syntax. The reason why I'd like to try it this way is because according to the MS docs, it will reverse the action if an error occurs. That sounds pretty cool to me. TIA

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The method and syntax works for me.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    For further context, these are MakeTable Queries that are tied to a blank access file in my documents folder. I then make two copies of that db file and store them in a backups folder and an external hd

  4. #4
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    Okay, on further reading of the docs, it says this particular method applies to access workspaces only (access 2013). I'm running the most current version of access so maybe this method won't work for me. I'm not entirely certain

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are creating tables in an existing db that does not have any tables? How is that blank db created?

    I just tested creating table in another db and it worked.

    I am using Access 2010. Not using workspace object. Just the code you posted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    I say blank. It started as a blank Access file. Now when I run the BU, it's filled with the tables from the day before. Our tables are hosted on SharePoint but SP has done some funky things in the past so I always keep copies of our tables locally just in case. According to the MS documentation, dbFailOnError applies to workspaces only, and workspaces applies to Access 2013. I've never used them before

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I never had to use but think workspace was around quite a while. Access 2013 may be last version to support. The dbFailOnError works for me.

    I have a procedure that deletes all records from the other db and inserts all new records.

    How do you manage table relationships? Are you not using autonumber key?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    SharePoint has auto number and lookup fields on the foreign key side and you can create your relationships in Access on the FE. For the most part, everything still works the same when using SP. One difference is the auto number isn't assigned until the record is saved, which allows for multiple users to enter data at the same time. You can't create multi field indexes in SP so I had to make up for that with code

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-16-2022, 03:27 AM
  2. Replies: 5
    Last Post: 06-01-2015, 10:18 AM
  3. Needing help with database setup.
    By roger123 in forum Access
    Replies: 3
    Last Post: 06-21-2012, 08:21 AM
  4. Needing Advice and Help with Table Layout
    By PeteW in forum Database Design
    Replies: 0
    Last Post: 03-12-2011, 11:40 PM
  5. Newby to Access Needing Advice
    By johnwyork in forum Access
    Replies: 2
    Last Post: 05-15-2010, 10:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums