[RESOLVED] VBA - Copy database from one destination to another

[Giftx]

Hello everyone,
I am working with MS Access databases (.mdb) Can anyone please give me a simple directive on copying a database from one destination to another (from Source to destination)
Actually I am intend to copy the database from a Server db to my local C:\ drive.

Below is my set up so far:

Private Sub cmddbCopy_Click()
Dim dbsource As Database
Dim dbdestination As Database

Set dbdestination = CurrentDb

Set dbsource = OpenDatabase("M:\\Livctrls03-08\data1\App\UCCProd\Databases\Unclaimed Checks.mdb", True, True)
Set dbdestination = OpenDatabase("C:Unclaimed Checks.mdb", True, False)

Any hint will be greatly appreciated.
Giftx.

[mendhak]

If this is a simple task to copy the entire database over, then because it's an MDB, use the FileCopy method to copy the MDB over.

[Giftx]

Thanks for your quick response.
Question: should I create the destination database already then copy over the data or will it just copy the entire database and name it?

Also, can you give me a code example
Thanks.
GiftX.

[mendhak]

MS Access databases are simply .MDB files. Treat it like any other file. This means that you needn't create the database first, just overwrite it if the file already exists.

[RobDog888]

To be safe you will want to make sure no users are in hte db too.

[Giftx]

Please, can you give me an example of what you mean? Also, you can see an excerpt of the code I started in my thread.
Giftx.

[RobDog888]

Are you doing this from within Access' VBA IDE?

[Giftx]

Hi Rob.
Yes, I am doing this from MS Access environment.

[Siddharth Rout]

Hi Giftx

If you are copying the mdb as any other file then check these three ways... take your pick

1) API WAY

you can use this API. this works in vb6. I am sure this will work in vba as well.

Option Explicit

Private Type SHFILEOPSTRUCT
        hwnd As Long
        wFunc As Long
        pFrom As String
        pTo As String
        fFlags As Integer
        fAnyOperationsAborted As Long
        hNameMappings As Long
        lpszProgressTitle As String
End Type

Private Declare Function SHFileOperation Lib "shell32.dll" _
Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10
Private Const FO_COPY = &H2

Public Function ShellFileCopy(src As String, dest As String, _
    Optional NoConfirm As Boolean = False) As Boolean

'PURPOSE: COPY FILES VIA SHELL API
'THIS DISPLAYS THE COPY PROGRESS DIALOG BOX
'PARAMETERS: src: Source File (FullPath)
            'dest: Destination File (FullPath)
            'NoConfirm (Optional): If set to
            'true, no confirmation box
            'is displayed when overwriting
            'existing files, and no
            'copy progress dialog box is
            'displayed
            
            'Returns (True if Successful, false otherwise)
            
'EXAMPLE:  
  'dim bSuccess as boolean
  'bSuccess = ShellFileCopy ("C:\MyFile.txt", "D:\MyFile.txt")

Dim WinType_SFO As SHFILEOPSTRUCT
Dim lRet As Long
Dim lflags As Long

lflags = FOF_ALLOWUNDO
If NoConfirm Then lflags = lflags & FOF_NOCONFIRMATION
With WinType_SFO
    .wFunc = FO_COPY
    .pFrom = src
    .pTo = dest
    .fFlags = lflags
End With

lRet = SHFileOperation(WinType_SFO)
ShellFileCopy = (lRet = 0)

End Function

2) FSO WAY

Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "C:\Documents and Settings\user\My Documents" 'Folder file is located in
newPath = "C:\Documents and Settings\user\My Documents\Misc" 'Folder to copy file to
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "\" & "myfile.mdb", newPath & "\" & "myfile.mdb" 
Set fs = Nothing

3) FILE COPY

Dim sDest As String
Dim sSource As String

sSource = "\\MYSERVER\KOOLSID\MYFILE.MDB"
sDest = "C:\BACKUP\MYFILE.MDB"

FileCopy sSource, sDest

Hope this helps...

[RobDog888]

Ah I see you like the API way too I have code similar to that on the Forums but for folder copying.

Copying from within Access while its open will work but you will need to either read the ldb file to see if anyone else is in it or try other techniques. You will be limited since you are in Access already.

[Giftx]

Thanks all you Gurus. All the examples will do just fine.
Thanks and enjoy your weekend.
Giftx.

[anuj1781]

Hey All,

I have a question related to this.
I am using API way to copy data, but my user is on a VPN and VPN gets disconnected in between and excel throws out an error. Is there a way to copy data bit by bit from one location to another?