Generic copy all tables to another database

[Angus Comber]

Hello

I need to frequently backup an Access database to another backup file. The
access database only contains tables. ie no forms, etc. I realise I can
simply copy the file but the file is usually open so want to use SQL to copy
all tables. There are relationships so need to copy those too. I was
wondering if there was a general SQL statement to do this?

Or would I have to write DDL code for each table then SQL statements for
each table to copy the data.

If there were generic statements it would save me the maintenance effort and
upfront work too!

Any help would be greatly appreciated.

Angus

 

[Guest]

You can copy the whole MDB from the original location to the BackUp location

Dim X
X = shell ("xcopy c:\MDBName.mdb c:\BackUpDirectory")

 

[Douglas J. Steele]

There's a reason why most methods don't let you copy files that are in use.
In the case of databases, the copy of the database could be in an
inconsistent state, meaning that the backup is worse than useless (worse
because it may have incorrect data, not just incomplete data)

Far better would be to loop through the TableDefs collection, and use the
TransferDatabase method to copy all of the non-system tables:

Something like the following untested aircode:

Sub ListAllTables()
Dim tdfCurr As TableDef
Dim strBackupDatabase As String

strBackupDatabase = "C:\Backup\MyDatabase.mdb"

For Each tdfCurr In CurrentDb().TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strBackupDatabase, acTable, tdfCurr.Name, _
tdfCurr.Name
End If
Next tdfCurr

End Sub

 

[Angus Comber]

I realise I can do this. But as explained below I want to do the copy using
SQL.

Angus

 

[Angus Comber]

I would prefer to do this in SQL because some users will not have Microsoft
Access installed. So maybe DoCmd.TransferDatabase won't work.

Angus

 

[Douglas J. Steele]

SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

Ensure that newtable doesn't exist to use this.

 

[Larry Linson]

Is this a homework assignment that you must do it in SQL?

If not, but you want to do it programmatically, check Help on the Tables
collection and Transferdatabase and/or CopyObject. Check the Relations
Collection.

The basic steps would be this: Create a new Database, CopyObject the Tables
to the new Database, and, while I don't recall doing it, I presume you can
CopyObject the Relations Objects from the Relations Collection to the new
Database.

Larry Linson
Microsoft Access MVP