Member Avatar for tgifgemini

Good morning everyone,
can anyone give me example of how to programatically copy an existing database to another location and rename the copied version - sort of a backup.

I have a database name "DbProd" which is located on a server, and I want to copy this database to either the same server or my hardrive "C:\". Also I would like to rename the database to "DBBackup" or any name of my choice.

Thanks.
tgifgemini.

  • 8 Contributors
  • 8 Replies
  • 2K Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

Nice question, and solid starts from and . Two things usually cause the "Permission denied" that hit: the database file is still open (there is a Jet lock file next to it), or Windows is blocking you from writing to the target folder. Close every ADO/DAO connection, then check for a .ldb (or .laccdb) before copying. Also avoid copying into C:\ or Program Files unless your app is elevated; use a folder you can write to, or a UNC path with the right share/NTFS permissions.

If you want a one-step copy-to-new-name without a separate rename, call the Win32 API. It is fast, lets you overwrite if desired, and works across drives/UNC shares.

' In a module
Option Explicit
Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" _
    (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
     ByVal bFailIfExists As Long) As Long

Private Function HasJetLock(ByVal dbPath As String) As Boolean
    Dim p As Long, stem As String
    p = InStrRev(dbPath, "."): If p = 0 Then Exit Function
    stem = Left$(dbPath, p)
    HasJetLock = (Len(Dir$(stem & "ldb")) > 0) Or (Len(Dir$(stem & "laccdb")) > 0)
End Function

Public Function BackupDbFile(ByVal src As String, ByVal destFolder As String, _
                             ByVal newName As String, Optional ByVal overwrite As Boolean = True) As Boolean
    If HasJetLock(src) Then Err.Raise vbObjectError + 1, , "Database is open. Close it first."
    If Right$(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
    If Dir$(destFolder, vbDirectory) = "" Then MkDir destFolder
    BackupDbFile = (CopyFile(src, destFolder & newName, IIf(overwrite, 0, 1)) <> 0)
End Function

Example call:

Call BackupDbFile("\\server\share\DbProd.mdb", "C:\Backups", _
                  "DBBackup_" & Format$(Now, "yyyymmdd_hhnnss") & ".mdb")

Extras:

  • If it is an Access/Jet file, you can also create a clean backup using DAO’s CompactDatabase (close all connections first).
  • If it is a server database (e.g., SQL Server), do not file-copy the .mdf; use the engine’s BACKUP/RESTORE commands instead.

Recommended Answers

All 8 Replies

Member Avatar for venkatramasamy

HI,
there are so many methods to implement file copy,
as SCBWV said,
you can use Filecopy to copy the DB

ToRename the File try the Follwing code

Name "c:\temp\test.mdb" As "c:\temp\test1.mdb"

hope this code will help you

With regards
Venkatramasamy SN

Edited by happygeek because: fixed formatting
Member Avatar for tgifgemini

Thanks guys.
your input was quite understand and implemented successfully.
Happy new year to you all.
tgif.

Member Avatar for abub_k

hi , i want to copy my database file using VB 6 codes to another location. i used 'filecopy' command. But, an error "permission denied!" error is occuring. So, please help me to copy that database file in same propram....

Member Avatar for XtremeGFX

hi , i want to copy my database file using VB 6 codes to another location. i used 'filecopy' command. But, an error "permission denied!" error is occuring. So, please help me to copy that database file in same propram....

Function Exists(Filename As String) As Boolean
Exists = (Dir(Filename) <> "")
End Function

Sub CreateDBBackup()
Dim St As String, Data As String

If Dir("Backup", vbDirectory) = vbNullString Then MkDir "Backup"
St = App.Path + "\Backup\DBBackup.dat"
Open "Database.dat" For Binary Access Read As #1
Open St For Binary Access Write As #2
Data = Space(LOF(1))
Get #1, , Data
Put #2, , Data
Close #2
Close #1
End If

End Sub

Member Avatar for kinwang2009

I request tgifgimini to mark your thread as resolved.

Member Avatar for Dick_stgeorge

Good morning everyone,
can anyone give me example of how to programatically copy an existing database to another location and rename the copied version - sort of a backup.

I have a database name "DbProd" which is located on a server, and I want to copy this database to either the same server or my hardrive "C:\". Also I would like to rename the database to "DBBackup" or any name of my choice.

Thanks.
tgifgemini.

Public Sub Compact_DB()
    On Err GoTo DataError
   Dim Data As String
    'executed after closing databases
   ' 'The database is kept in the same folder as the program.
   ' 'Backup databases are in a backup folder       'E:\VB\VID_SS\COMMON\HELP

'TESTING:  Exit Sub
            
      Dim Oldname, Newname
    ' if dbhuntsman.
     
      With dbhuntsman
          .Close
      End With
      GoTo NoError
      
DataError:
        strError = Data
        Call Common_Error
        strReply = MsgBox("Error loading Golfer Excel File - " & Data, vbOKOnly)
        'GoTo GetNextRecord
NoError:
     dbHuntsman1.Close
     dbHuntsman2.Close
    'dbHuntsman3.Close
   
      'DBEngine.CompactDatabase strDefaultDB, "c:/huntsmanbackup/huntsmannew.mdb"
    If Dir(strBackupLocation & "/huntsmanbackup.mdb") <> "" Then
            Kill (strBackupLocation & "/huntsmanbackup.mdb")
        End If
        DBEngine.CompactDatabase strDefaultDB, strBackupLocation & "/huntsmanbackup.mdb"
        
      If Dir(strBackupLocation & "/huntsman3.mdb") <> "" Then
          Kill (strBackupLocation & "/huntsman3.mdb")
      End If
      If Dir(strBackupLocation & "/huntsman2.mdb") <> "" Then
            DBEngine.CompactDatabase strBackupLocation & "/huntsman2.mdb", strBackupLocation & "/huntsman3.mdb"
            'OldName = strBackupLocation & "/huntsman2.mdb": NewName = strBackupLocation & "/huntsman3.mdb"   ' Define file names.
          'Name OldName As NewName   ' Rename file.
      End If
    
      If Dir(strBackupLocation & "/huntsman2.mdb") <> "" Then
          Kill (strBackupLocation & "/huntsman2.mdb")
      End If
    
      If Dir(strBackupLocation & "/huntsman1.mdb") <> "" Then
             DBEngine.CompactDatabase strBackupLocation & "/huntsman1.mdb", strBackupLocation & "/huntsman2.mdb"
            'OldName = strBackupLocation & "/huntsman1.mdb": NewName = strBackupLocation & "/huntsman2.mdb"   ' Define file names.
          'Name OldName As NewName   ' Rename file.
      End If

If Dir(strBackupLocation & "/huntsman1.mdb") <> "" Then
          Kill (strBackupLocation & "/huntsman1.mdb")
      End If
    
      If Dir(strBackupLocation & "/huntsmanBackup.mdb") <> "" Then
          DBEngine.CompactDatabase strBackupLocation & "/huntsmanBackup.mdb", strBackupLocation & "/huntsman1.mdb"
      End If
    If Dir(strBackupLocation & "/huntsmannew.mdb") <> "" Then
        Kill (strBackupLocation & "/huntsmannew.mdb")
        Oldname = strBackupLocation & "/huntsmannew.mdb": Newname = strBackupLocation & "/huntsman.mdb"   ' Define file names.
        Name Oldname As Newname   ' Rename file.
    End If
    
End Sub
Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
Member Avatar for kvprajapati

We appreciate your help. Have you ever noticed that the current thread is two years old? Please do not resurrect old threads and have a look at forum rules. Please read before posting - http://www.daniweb.com/forums/thread78223.html


Thread Closed.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.