Copy Database File And Save to Desktop

**pr4t3ek** · 11-13-2008, 03:24 AM

Hi,
Let's say I have a database called testTemplate.mdb in c:\ which is a template-like database where i import data to.

Is there a way for me to copy the testtemplate.mdb and past it to my desktop with file name test.mdb.

I don't want to copy any data from the database, just the table's + queries

I would like this to be done via a macro... In order for me to execute other macro's to copy data from excel to that test.mdb file on desktop.

**qff** · 11-13-2008, 01:02 PM

Hi

something like this might work. It makes a copy of the database then deletes any table relationships (otherwise table records can't be deleted) and then deletes table records.

Sub copymyDB()

Dim Sourcedb As String
Dim Destdb As String
Dim mydb As DAO.Database
Dim mytable As TableDef
Dim i As Integer
Dim rls As Relation
Dim mySQL As String

Sourcedb = "C:\TestTemplate.mdb"
Destdb = "C:\test.mdb"

'Copy Database
FileCopy Sourcedb, Destdb

Set mydb = DBEngine.OpenDatabase(Destdb)

With mydb

'Delete any Relationships
    For Each rls In .Relations
        mydb.Relations.Delete (rls.Name)
    Next

'Delete Table Records
    For Each mytable In .TableDefs
        If Left(mytable.Name, 4) <> "MSys" Then
            mySQL = "Delete * FROM " & mytable.Name & " in '" & Destdb & "'"
            .Execute mySQL
        End If
    Next

End With