Transfer Database

[LadyDev]

I don't know if this is the right forum, but here goes. I have two command buttons on my form, one I want to be able to transfer a database that is located on my hard drive to another location (copy to disk) and the other I want to just transfer the database to another folder located on the same hard drive. My question is can I use the below code for each command button (coded seprately) to perform the above functions. I know it references 'a table', but can it be changed to move the whole database?


Sub sExportExternal(strDBFrom As String, strDBTo As String, strTableName As String)
' Procedure to transfer a table from one external Access database to another
' Accepts:
' strDBFrom - the name and path of the database that contains the table to be exported from
' strDBTo - the name and path of the database that the table is to be exported to
' strTableName - the name of the table that is to be exported
On Error GoTo E_Handle
Dim objAccess As New Access.Application
With objAccess
.OpenCurrentDatabase (strDBFrom)
.DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable, strTableName, strTableName
.CloseCurrentDatabase
End With
sExit:
Exit Sub
E_Handle:
Select Case Err.Number
Case 3011 ' The table does not exist in the first database
MsgBox "'" & strTableName & "' does not exist in '" & strDBFrom & "'", vbOKOnly, "Transfer cancelled"
Case 3044 ' The database that we are transferring the table to does not exist
MsgBox "'" & strDBTo & "' does not exist.", vbOKOnly, "Transfer cancelled"
Case 7866 ' The database that we are transferring the table from does not exist
MsgBox "'" & strDBFrom & "' does not exist.", vbOKOnly, "Transfer cancelled"
Case Else
MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
End Select
Resume sExit
End Sub

 

[vbajock]

If you are just trying to make a copy, why don't you just shell to xcopy?

 

[LadyDev]

How does the shell command work and where do I find it?

I am not exactly making a copy per se. I want to move older databases from one folder to another. The other folder is to be used for archiving. 'Moving' physically removes the file from the folder (I think). If I make a copy I'll have to go back to the original folder a delete the file. Plus I need confirmation of the move.

But if the Shell Command does this -- that's fine.

thanks!

 

[ghudsoncc]

Try...

'copy the file
FileCopy ("C:\Databases\DB_A.mdb&quot, ("X:\Archive\DB_A.mdb&quot
'Delete the file
Kill ("C:\Databases\DB_A.mdb&quot

HTH

 

[LadyDev]

Okay I have a few hundred dbs in this folder. That code seems specific to one db at a time.

Where do I put this code in the AfterUpdate event of the form?

 

[vbajock]

Shell is in the help file.

You might want to look into the FileSystemObject for moving mass amounts of files around.

 

[BoulderRidge]

I don't know whether the code in your post was original or not, but thanks either way for posting it. It was exactly what I have been hunting for for the past hour+ to copy a table between two other databases from a third one. I did my best to credit you in the comment section of my code--thanks again!

--BoulderRidge