Exporting tables from non-current database

[way2bord]

I have a number of database that I would like to backup table data.

I would like to use a single database (db1) to cycle through the others (dbA-dbZ) and export all the tables to text/spreadsheet/etc.

Current process:

db1 opens dbA
....OpenDatabase(dbA...)
db1 imports all tables from dbA
...loop through tabledefs
...DoCmd.TransferDatabase acImport
db1 exports tables to file
...DoCmd.TransferText/Spreadsheet/etc.
db1 deletes tables from dbA
...TableDefs.Delete
db1 closes dbA
Loop through all databases

Is there a more efficient process for doing this?

Current process generates a large number of Access Security Notice warnings and seems inefficient.

 

[spikepl]

What is the point of "backup table data", and also backing it up to "text/spreadsheet/etc."?

 

[boblarson]

What is the point of "backup table data", and also backing it up to "text/spreadsheet/etc."?

My question as well. What is the purpose when you can just have backups of the database file itself?

 

[way2bord]

Some users don't have MS Access. Others would simply prefer to see / manipulate the data using non-access programs. Text backups are significantly smaller than a database duplicate.

There's a number of reasons, the simplest being...shit rolls down hill.

 

[boblarson]

Some users don't have MS Access. Others would simply prefer to see / manipulate the data using non-access programs. Text backups are significantly smaller than a database duplicate.

There's a number of reasons, the simplest being...shit rolls down hill.

Fair explanation. That's why it is good to share the WHY you are trying something because we typically are trying to provide the best solutions we can think of based on the needs.

A more efficient way would be to not import the tables to db1 at all. Just export them from dba but using db1 as the controller. Here's how you can do that:

Dim objAcc As Access.Application
Dim varItem As Variant
 
Set objAcc = OpenDatabase("PathAndFileNameHere")
 
   For Each varItem In objAcc.CurrentProject.AllTables
      If Left(varItem.Name, 1) <> "~" And Left(varItem.Name, 4) <> "MSys" Then
      objAcc.DoCmd.TransferSpreadsheet acExport, , varItem.Name, "C:\SomeFolder\" & varItem.Name & ".xls", True 
   Next
objAcc.CloseCurrentDatabase
objAcc.Quit
 
Set objAcc = Nothing

 

[way2bord]

Thanks. That's much simpler.
I completely forgot I could .doCmd from the application... -.-*