Coping tables between two Databases

[Mark Jones]

I want to create a new Access Database and
then copy whole tables with data to the new
database from my current database. I need
to do this through VBA, not the menu import
feature. I can create a new DB with the
CreateDatabase object, but I can't find an
example of how to copy a table from one DB to another.

Thanks for the help!

Mark Jones

 

[MattGilbert]

Check out TransferDatabase in the help files - it might do what you want.

 

[Hoa Le]

One alternative method is to backup the database by using the Filecopy statement in Access. Then use the Name statement to give the backup db with a new name. Here's some sample code behind a form with a textbox named [newpath] requiring input of the file to be backed up and a button cmdOK:

'--------------------
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim strNewDBName As String
Dim strOldDbName As String
Dim strOldDBPrefix As String
Dim fyear As String
Dim fmonth As String
Dim fday As String
Dim iloc As Integer
Dim x As Integer

strOldDbName = Me![newpath]

For x = Len(strOldDbName) To 1 Step -1
iloc = InStr(x, strOldDbName, "\")
If iloc <> 0 Then
Exit For
End If
Next x

strOldDBPrefix = Mid$(strOldDbName, iloc + 1, 2)

DoCmd.Hourglass True

' copy database
strNewDBName = CurDir$ & "\.mdb"
FileCopy strOldDbName, strNewDBName

' rename backup database
fyear = DatePart("yyyy", Now)
fyear = Mid$(fyear, 3, 2)
fmonth = DatePart("m", Now)
If Len(fmonth) = 1 Then
fmonth = "0" & fmonth
End If
fday = DatePart("d", Now)
If Len(fday) = 1 Then
fday = "0" & fday
End If

' rename backup database
Name strNewDBName As CurDir$ & "\" & strOldDBPrefix & fyear & fmonth & fday & ".mdb"

DoCmd.Hourglass False
DoCmd.Beep
MsgBox "The database has been backed up.", 64


Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Str(err)
MsgBox Error$
Resume Exit_cmdOK_Click

End Sub
'-------------------------

For example, enter in the textbox ---> C:\FolderName\DBName.mdb will backup the DBName.mdb with a new file DB000508 where DB is the 2 leftmost strings of the file, 00 is last 2 digits of year 2000, 05 is month of May and 08 is current day. Of course, you can modify the sample code to fit your case.

Good luck