Copy db

You might  be able to modify this code. At the moment it copies the current (frontend)

http://www.mvps.org/access/api/api0026.htm

Hi again Terry!
Can You give a novice some guidelines in how to use this code and how to call the function?

I will see if I can put an example together and get back.

This is to keep things easy, to make a complete solutionwould take some time. The problem is that the backend can be anywhere, so you need code to get the location of the backend. In this example I have just coded it directly into the code. You can see which lines are of importance with this beside '<<<<<<<<<<<<<<<<<<<<<<<<<<

In this example it uses the same name for the source (FROM )and the destination (TO) which just makes a copy as you will see.

FIRST you need to alter the first line where '<<<<<<<<< is beside to contain the path/filename of your backend. If you want to copy the database to another folder/file then you need to alter the line starting with .pTo

Now place a button on a form and in the on Click event you just write

GetBackend


Try it!



'********** Code Start *************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

Private Const FO_MOVE As Long = &H1
Private Const FO_COPY As Long = &H2
Private Const FO_DELETE As Long = &H3
Private Const FO_RENAME As Long = &H4

Private Const FOF_MULTIDESTFILES As Long = &H1
Private Const FOF_CONFIRMMOUSE As Long = &H2
Private Const FOF_SILENT As Long = &H4
Private Const FOF_RENAMEONCOLLISION As Long = &H8
Private Const FOF_NOCONFIRMATION As Long = &H10
Private Const FOF_WANTMAPPINGHANDLE As Long = &H20
Private Const FOF_CREATEPROGRESSDLG As Long = &H0
Private Const FOF_ALLOWUNDO As Long = &H40
Private Const FOF_FILESONLY As Long = &H80
Private Const FOF_SIMPLEPROGRESS As Long = &H100
Private Const FOF_NOCONFIRMMKDIR As Long = &H200

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

Function fMakeBackup() As Boolean
Dim strMsg As String
Dim tshFileOp As SHFILEOPSTRUCT
Dim lngRet As Long
Dim strSaveFile As String
Dim lngFlags As Long
Const cERR_USER_CANCEL = vbObjectError + 1
Const cERR_DB_EXCLUSIVE = vbObjectError + 2
    On Local Error GoTo fMakeBackup_Err

    If fDBExclusive = True Then Err.Raise cERR_DB_EXCLUSIVE
   
    strMsg = "Are you sure that you want to make a copy of the database?"
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Please confirm") = vbNo Then _
            Err.Raise cERR_USER_CANCEL
           
    lngFlags = FOF_SIMPLEPROGRESS Or _
                            FOF_FILESONLY Or _
                            FOF_RENAMEONCOLLISION
   
    strSaveFile = "C:\Test\Backend.mdb" '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   
    With tshFileOp
        .wFunc = FO_COPY
        .hwnd = hWndAccessApp
        .pFrom = strSaveFile & vbNullChar '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        .pTo = strSaveFile & vbNullChar  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        .fFlags = lngFlags
    End With
    lngRet = apiSHFileOperation(tshFileOp)
    fMakeBackup = (lngRet = 0)
   
fMakeBackup_End:
    Exit Function
fMakeBackup_Err:
    fMakeBackup = False
    Select Case Err.Number
        Case cERR_USER_CANCEL:
            'do nothing
        Case cERR_DB_EXCLUSIVE:
            MsgBox "The current database " & vbCrLf & CurrentDb.Name & vbCrLf & _
                    vbCrLf & "is opened exclusively.  Please reopen in shared mode" & _
                    " and try again.", vbCritical + vbOKOnly, "Database copy failed"
        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: fMakeBackup" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            MsgBox strMsg, vbInformation, "fMakeBackup"
    End Select
    Resume fMakeBackup_End
End Function

Private Function fCurrentDBDir() As String
'code courtesy of
'Terry Kreft
Dim strDBPath As String
Dim strDBFile As String
    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    fCurrentDBDir = Left(strDBPath, InStr(strDBPath, strDBFile) - 1)
End Function

Function fDBExclusive() As Integer
Dim db As Database
Dim hFile As Integer
    hFile = FreeFile
    Set db = CurrentDb
    On Error Resume Next
    Open db.Name For Binary Access Read Write Shared As hFile
    Select Case Err
        Case 0
            fDBExclusive = False
        Case 70
            fDBExclusive = True
        Case Else
            fDBExclusive = Err
    End Select
    Close hFile
    On Error GoTo 0
End Function
'************* Code End ***************

you can always use the File Open/Save Dialog to choose the location of your baceknd/backup files.


There is also an example on ThomasJepsen's homepage with code to re-link your backend database. This code has a function which can be used to find the backend path/filename. The function is in the module named Attachments and is named GetBackend.

THis code gives you the Path/Filename of the First backend table. If you have more than one then it doesnt work.

Hi Terry!
From what I understand, the code has to be stored as an module. Is that right and what name should I give it? If I just write GetBackend as an instruction on a click event, I get an error message saying "Kompile error: An variable or procedure is expected. Not a module."
What am I doing wrong?

yes place the code in a module. You can save the module with any name you want. The module is just a holding place for the code.

Its hard to say what you are doing wrong as I can se what you  have done so far.

GetBackend??? Have you taken this from Thomas's example?

Hi Terry!
In Your answer, including the code, You wrote "Now place a button on a form and in the on Click event you just write

GetBackend"
When I click the button, the instruction must say something more than just GetBackend, must it not?

Sorry! I meant fMakeBackup :o)

You can see in the code above

Function fMakeBackup() As Boolean

This is the function which you use, and it uses the other functions which start with Private.

If you can git this working then perhaps wee can go on to using the File Save dialog after.

Now You'r talking. It works greate. Now, insted of saving the copy where the original is, how do I call a dialog form where I can direct where to save the copy?
Then I want to be able to fetch a copy and put it back and replace the existing one.
The path to the original will always be the same.
In total I feel this will be worth far more than the original 30 points.

On a second thought, it seems impossible to owerwrite the existing backend since the frontend is running, or can this be done if a form is open that is'nt using data from the backend?

Have you looked at Thomas's File save dialog?


The line .pTo = strSaveFile & vbNullChar  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

would need to be replaced to something like this


.pTo = FunctionToGetFileSaveNameGoesHere & vbNullChar


I havent looked too closely at it but the example (Thomas's) which re-links the backend, could maybe be used to replace the backend with another file.
An idea would to be to place the new backend at another path/filename using the File OPEN dialog, which I am sure is included in the example, and then re-link to the new backend. Re-linking to the new backend will be allowed, but if you just try overwriting the original backend with the new the this could very well give problems.

I'm rather busy right now, but I'll take a look once in a while and see if I can help.

Hi Terry!
I have solved everything exept for directing a copy to a folder by choice.
In the same way as the  File Open dialog opens when I want to import data, I want a dialog to open where I can direct a copy taken of the backend.
I have tryed Thomas example "FindSpecialPath" but I do'nt fully understand how to use it. He writes that a button with the instruction "?GetSpecialPath" will give the path to Desktop, My documents etc etc, but how.
Why is it a questionmark in the beginning? The debugger automaticly changes i to "Print", but otherwise exepts it.

Hi again!
I have made a new question which is ment for You to score some extra points.
http://www.eksperten.dk/spm/648240

I'll take a look at the file save problem you have this evening if I get time.

I have seen the other problem but dont really have any suggestions.


and thanks for the points

Thank You, Terry!
I'm very much appreciating Your help.

Hi again
I havent tested this so you may need to play around yourself.

Import the module CommonDialog into your database from Thomas's example.

Now you need a button and a field on your form. Give the field the name Sti

Now in thé buttons On Click event you need some code something like this

Dim dlg As New CommonDialog
  Dim StrFilter As String
  StrFilter = dlg.AddFilterItem(StrFilter, "Access-filer (*.mdb)", "*.mdb")
  dlg.DialogTitle = "Gem fil"
  dlg.filter = StrFilter
  dlg.ShowOpen
  Me!Sti = dlg.Filename


Now you can choose the Folder and File Name where you want to save the file to.

Now in the code which copies the database alter the line

.pTo = FunctionToGetFileSaveNameGoesHere & vbNullChar

To

.pTo = Me.sti & vbNullChar

and if the code is NOT in the same form then

.pTo = [forms]![YourFormNameGoesHere]![Sti] & vbNullChar

Hope you can get it to work

Thank You.
I will test it and let You know how it turns out.

After trying in several differnt ways, I'm giving up.
Can I use .pTo = and something that always saves the copy on the deskboard?

Is'nt it possible to open the Save as dialog instead of the Find file dialog?

the Save Dialog as far as I know is just the same as the find (file open) dialog. It just allows you to browse to a folder and choose a file name. Once you have the path/file name its your program wich takes over to do the actual saving/opening.

If you can send me what you have then maybe I can alter it so that it uses the Save/open dialog.

eskperten@NOSPAMsanthell.dk remove NOSPAM

I'm rather busy, also over the weekend but I should be able to find a bit of time.

I'm also busy today so I will send You something later today.

I have sent You a db with just the form I will use. I did'nt send a copy of my total db bacause It has grown to big.

I havent received anything, can you ZIP it?

and make sur ethat you remove NOSPAM from the email address

I have resent it, using mailaddress eskperten@santhell.dk.
I have not recieved any error message from the first one.

Did'nt You write the mailaddress wrong. Should'nt it be eksperten@..., instead of eskperten@..?
I have recent th db again.