Getting DoCmd in the correct scope or an alternative to exporting all the tables in dozens of Access databases

user_3123109 0
Viewed 72

What I'm trying to do is pretty simple:

  • Iterate through all of the .accdb files in a directory
  • Open them and export all the tables to separate .xlsx files

The issue I'm running into is the scope of DoCmd and I'm having difficulty changing it. The issue is coming up because I'm running the VBA from what is basically and empty Database1.accdb. When the VBA gets to DoCmd.TransferSpreadsheet, it is using CurrentDb and not the Access file that was opened with OpenDatabase. It is saying a table that is clearly does see (Debug.Print(tbl.Name) works) doesn't exist.

See below:

Option Compare Database

Public Const PROJECT_PATH = "\\192.168.1.2\Migration\"
Public Const DESC_PATH = PROJECT_PATH & "Descriptions\"
Public Const DESC_DB_PATH = DESC_PATH & "DBs\"
Public Const DESC_EXPORTS = DESC_PATH & "Exports\"
Sub ExportDescriptionTables()

    Dim wrkSpace As Workspace, _
        db As Database, _
        tbl As TableDef

    ' ************************** TEMPLATE FILES - BEGIN *************************
    Set dbDirObj = CreateObject("Scripting.FileSystemObject")
    Set dbFolder = dbDirObj.GetFolder(DESC_DB_PATH)
    Set dbFiles = dbFolder.Files
    ' ************************** TEMPLATE FILES - END ***************************

    ' Create the Access object for this file
    Set wrkSpace = CreateWorkspace("", "admin", "", dbUseJet)

    ' Iterate through each Access file
    For Each dbFile In dbFiles
           
        ' Open each Access database file
        Set db = wrkSpace.OpenDatabase(DESC_DB_PATH & dbFile.Name)
         
        ' Iterate through each table
        For Each tbl In db.TableDefs
        
            ' Export based on the table name matching specific criteria
            If Left(tbl.Name, 1) <> "~" And Left(tbl.Name, 4) <> "MSys" Then
            
                Debug.Print (tbl.Name)
                ' ################### THIS WHERE THE ERROR OCCURES ############## 
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, tbl.Name, DESC_EXPORTS & tbl.Name & dbFile.Name, True, tbl.Name
            End If
            
        Next tbl
        
        ' Close database
        db.Close
                
        ' Clear out access object for this file
        Set db = Nothing
    Next dbFile

End Sub

So my questions:

  1. Is there a way I'm simply not seeing for setting the scope of DoCmd?
  2. If not, what would be a better approach to this (perhaps running from Excel potentially forcing the CurrentDb to be the only open Acccess DB, which should bring DoCmd into proper scope)?
ms-access vba
2 Answers
  1. No. Always think of DoCmd as something a user can do via the user interface.
  2. When located a database and the table(s) within this to export, link this/these to the main database (with DoCmd.TransferDatabase), then export each linked table and delete it afterwards.
user_35272970

DoCmd is a child of an Application object. You can create a new Access application session and open your target database there. Then when you call DoCmd.TransferSpreadsheet within that session, it will look within its "own" database for the names of the tables (or queries) you ask it to export.

Here's a simple example, which exports just one table from one database.

Dim objAccess As Object

Set objAccess = CreateObject("Access.Application")
'objAccess.Visible = True ' useful during development and for trouble-shooting

objAccess.OpenCurrentDatabase "C:\Temp\Northwind_2007.accdb"
objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "Customers", "C:\Temp\Customers.xlsx"

objAccess.CloseCurrentDatabase
objAccess.Quit
user_773350
Related