Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

Database Daily Backup

If your Database is installed on a Local Area Network (LAN) location then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept them in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deleted by mistake etc., you can always send a request to the Computer Department giving details of location, database name and safe backup date from which you would like to restore from. This may take few hours to few days to get it done, because the backup tapes or other mediums must be transported back from its storage location before they are able to complete your request.

If your Project have its own backup procedure and does it on regular basis then you don't have to go after Network backups, causing delays in restoring the Database. This will also ensure that your Application's down time is very minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but when it comes to the safety of the Database File itself this will not work. When the database is on a Network location several Users have access rights to that folder, besides your Application Users, and the database is not safe there.

You cannot make a Database Read-Only under Network Security to protect it from inadvertent loss. If you do that then you cannot work with the Database.

As a precautionary measure we can take a quick Daily Backup of the Database File, from Server to Local Disk or vice versa with a DOS Batch File. The Backup should run immediately on opening the Database for the first time of the day, by any one of the users.

We can do this with a simple VBA Routine to create a DOS Batch File in the database folder and run it from there to make a copy to local drive. We need a small table with a single record to keep track of the Backup event. The backup program should run only once a day, when the database is open for the first time of the day, by any one of the users and should prevent the program from running on subsequent shut-downs and re-opening events.

  1. Create a Table with the following structure and save it with the name Bkup_Ctrl and add a single record with a date earlier than today in the bkupdate Field. Leave the other field blank.
    Table : Bkup_Ctrl Structure
    Srl. Field Name Type Size
    1. bkupdate Date/Time  
    2. workstation Text 20
    Table : Bkup_Ctrl
    bkupdate workstation
    01/05/2008 PC1-1234
  2. Copy and Paste the following Code into a Global Module of your Project and save the Module.
    Public Function SysBackup()
    '------------------------------------------------------'
    'Author: a.p.r. pillai
    'Date  : 01-Apr-2008
    'URL   : http://www.msaccesstips.com
    'All Rights Resersed by msaccesstips.com
    '------------------------------------------------------
    Dim dbPathName, j As Long, t As Date
    Dim bkupdate, strBatchFlle As String, qot As String
    
    On Error GoTo sysBackup_Err
    
    qot = Chr$(34)
    bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
    ' bkupdate+7 > date() for weekly backup
    If bkupdate = Date Or bkupdate = 0 Then
        Exit Function
    End If
    
    dbPathName = CurrentDb.Name
    j = InStrRev(dbPathName, "\ ")
    
    If j > 0 Then
        strBatchFlle = Left(dbPathName, j)
        strBatchFile = strBatchFlle & "bakup.bat"
        Open strBatchFile For Output As #1
            Print #1, "@Echo off"
            Print #1, "Echo :------------------------- "
            Print #1, "Echo : " & dbPathName
            Print #1, "Echo Daily Backup to C:\ "
            Print #1, "Echo :------------------------- "
            Print #1, "Echo : "
            Print #1, "Echo :Please wait... "
            Print #1, "Echo : "
            Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\ " & qot
    'add lines here for Back-end database or for other Files
        Close #1
    
    'Copy file
        Call Shell(strBatchFile, vbNormalFocus)
        t = Timer
        Do While Timer <= t + 10
           DoEvents 'wait for 10 seconds
        Loop
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"        DoCmd.SetWarnings True
    
        'Kill strBatchFileEnd If
    
    sysBackup_Exit:
    Exit Function
    
    sysBackup_Err:
    MsgBox Err.Description, , "sysBackup()"
    Resume sysBackup_Exit
    End Function
    
  3. Add the following line of Code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other Form that opens immediately after loading the Database.

SysBackup

At the beginning of the SysBackup() routine, the Program reads the last backup date from the Bkup_Ctrl Table and checks whether it matches today's date, if it does then stops the program from proceeding further. By replacing the expression bkupdate = date() with the expression bkupdate+7 > date() you can schedule the Backup to run on weekly intervals on a particular Day of the Week.

The VBA Routine creates a DOS Batch File in the same folder of your Database and Runs it. The DOS Copy command is used for copying the Database File to the User's local drive. Even though a VBA FileCopy() Function is available, this will not work from within to make a copy of the same Database.

You may modify the line to change the Target Location C:\ to a different one, if needed.

A delay loop is built into the routine to slow down the program for about 10 seconds, to give enough time for the DOS Command to complete copying the Database. Normally, the VBA Code execution will not wait for the DOS Command to complete before executing the next statement. This will also prevent the User from start working with the Database before the copy operation is complete. You may increase or decrease this value depending on the size of the Database, or after trial runs of the procedure to determine approximate time it takes to copy.

The control table's bkupdate Field is updated with current date immediately after completion of the Copy operation and this will prevent further running of this procedure in subsequent Sessions on the same day. If your Application has a Back-End Database then install this table in there and link it to the Front-End. If your Application is on a Network and shared by several Users then by referring the workstation field you can easily find out which machine have the latest Backup Copy.

The Kill strBatchFile statement (if enabled) will delete the DOS Batch File after the backup operation. The delay loop protects the DOS Batch File from this statement for about 10 seconds. Enable this line if you don't want the batch file to remain in the database folder.

You can create a DOS Batch file manually, with a Text Editor like Windows Notepad, install it in the Database Folder and run it from Code or Macro. You may define the Source and Target Locations manually for the Copy command.

The advantage of the above Code is portability and convenience. You can copy the Code and the bkup_ctrl Table into your other Projects and run it without much change or worrying about the Source or Target Location addresses of the Database.




Download Demo Database



Share:

68 comments:

  1. I'm facing the below error:

    ((Path/File access error))
    ((OS Warpping rule MOR-3-1
    MSOffice_File_Write has blocked C:\Program File\Microsoft Office\Office\MSACCESS.EXE trying to access K:\ES\bakup.bat))

    I know that it related some security settings, could you please help?

    Thanks,
    Nadia

    ReplyDelete
  2. Which Version of Windows you are using. We have Windows2000 installations on Workstations and WindowsNT on Network and Batch Files run without problem.

    Ask your System Administrator to Join you temporarily as your Workstation Administrator and try running the backup procedure. Batch Files comes under the Category of Program Files (.exe).

    It should work even with Normal security policy on Workstation.

    Regards,

    ReplyDelete
  3. Hi,

    I would like to use this to do a daily backup to a subfolder and need to add the date to the name of the file - and cant get around the default date format that uses forward slashes (mm/dd/yyyy = illegal ). Can you help?

    Open strBatchFile For Output As #1
    Print #1, "@Echo off"
    Print #1, "Echo :Today is " & Date
    Print #1, "Echo : "
    Print #1, "Echo :------------------------- "
    Print #1, "Echo :File being backed up is " & dbPathName
    Print #1, "Echo : Daily Backup to C:\testDB\backups"
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : "
    Print #1, "Echo : Please wait... "
    Print #1, "Echo : "
    Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\testDB\backups" & qot
    Print #1, "Pause"
    'add lines here for Back-end database or for other Files
    Close #1

    ReplyDelete
  4. Add one more Variable in the declaration at the beginning of the routine

    dim target

    before the Open statement write the following:

    target = dir(dbPathName)
    target = left(target,len(target)-4) & format(date(),"mm-dd-yyyy") & ".mdb"


    rewrite the following statement:

    Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\testDB\backups\" & target & qot

    ReplyDelete
  5. hello dear Mr.
    How are u
    thanks for your helping
    i did all of mention sections but it is not working for me

    1- I made a above table : Question : what is "Srl." name in table ?
    2- I did copy above code in Module name is "Module"
    3- I have a 2 bootable form "Autorun" that Run in same time at first open and i Did Create a "SysBackup" in Open Event and set this "Call SysBackup" like this

    Private Sub Form_Load()
    Call SysBackup
    End Sub

    So a Hope to work it for me "Daily Backup"

    But form 2 days ago to now it is not making any backup
    Please Help me

    Note : i want , it work backup in this location : \\Servername\folder\ With This name : Databasename_Date()format

    Thanks for you help
    Best Regards
    Helpppppppppppppppppppppppppppppppp

    ReplyDelete
  6. Hi,

    You can ignore the "Srl." Column while creating the Table. This was only to indicate the number of fields required in the Table. You can see in the next image with the data shows only bkupdate and workstation fields. 

    I assume that you have copied the main Code into a Standard Module and not in the Form Module. 

    You may create a small database and import the Form that calls the SysBackup() Program, the main Program that you have copied and pasted from this page and the Backup control table.  

    Use Winzip and zip the database along with the Backup Batch File and email to me: aprpillai48@gmail.com. 

    Let me have a look at your problem, correct them if necessary and send it back to you in no time. 

    Regards,

    ReplyDelete
  7. Hi dear again
    You are very kind for me but this is part of a domain location
    thant i have an admin of it
    you said i should copy the main Module to my Bootable form ?????
    I did copy it on the Main Module and Call it from the Bootable form .

    Ok i try agian with this method and call back
    thanks for your Informations
    Regards
    Kiss

    ReplyDelete
  8. I have a more Question Please See .

    I have a code to for a command for backup
    this is code :
    I did it Too , check mark 'Microsoft Scripting Runtime' for the CopyFile piece to work!
    But it is not work
    it is an error in fso.CopyFile Line
    I don't undrestand it


    Private Sub BackupCopy_Click()

    'This function will allow you to copy a db that is open,

    Dim fso As FileSystemObject

    Dim sSourcePath As String
    Dim sSourceFile As String
    Dim sBackupPath As String
    Dim sBackupFile As String

    sSourcePath = "D:\Access\Mainta Database"
    sSourceFile = "Mainta.mdb"
    sBackupPath = "D:\Access\Mainta Database\Bck"
    sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

    Set fso = New FileSystemObject
    fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
    Set fso = Nothing

    Beep
    MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"

    End Sub

    ReplyDelete
  9. in the form Load i did this

    But it has an error in this line strBatchFile = strBatchFlle & "bakup.bat"

    Private Sub Form_Load()
    '------------------------------------------------------
    'Author: a.p.r. pillai
    'Date : 01-Apr-2008
    'URL : http://www.msaccesstips.com
    'All Rights Resersed by msaccesstips.com

    Dim dbPathName, j As Long, t As Date
    Dim bkupdate, strBatchFlle As String, qot As String

    On Error GoTo sysBackup_Err

    qot = Chr$(34)
    bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
    ' bkupdate+7 > date() for weekly backup
    If bkupdate = Date Or bkupdate = 0 Then
    Exit Sub
    End If

    dbPathName = CurrentDb.Name
    j = InStrRev(dbPathName, "\ ")

    If j > 0 Then
    strBatchFlle = Left(dbPathName, j)
    strBatchFile = strBatchFlle & "bakup.bat"
    Open strBatchFile For Output As #1
    Print #1, "@Echo off"
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : " & dbPathName
    Print #1, "Echo :Daily Backup to \\Mainta-sql\Mainta\BackupDaily\ "
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : "
    Print #1, "Echo :Please wait... "
    Print #1, "Echo : "
    Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "\\Mainta-sql\Mainta\BackupDaily\ " & qot
    'add lines here for Back-end database or for other Files
    Close #1

    'Copy file
    Call Shell(strBatchFile, vbNormalFocus)
    t = Timer
    Do While Timer <= t + 10
    DoEvents 'wait for 10 seconds
    Loop
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"
    DoCmd.SetWarnings True

    'KillstrBatchFile
    End If
    sysBackup_Exit:
    Exit Sub

    sysBackup_Err:
    MsgBox Err.Description, , "sysBackup()"
    Resume sysBackup_Exit

    End Function

    ReplyDelete
  10. Sory again in your code you used of " strBatchFlle "
    is this true ???????

    See strBatchFlle or strBatchFile ???? which one ? is currect

    ReplyDelete
  11. This may not work from within the database. Make few changes in the following lines of your code and try again. Add a backslash at the end of the first and third line as I have shown below and retry it.

    sSourcePath = “D:\Access\Mainta Database\”
    sSourceFile = “Mainta.mdb”
    sBackupPath = “D:\Access\Mainta Database\Bck\”
    sBackupFile = “BackupDB_” & Format(Date, “mmddyyyy”) & “_” & Format(Time, “hhmmss”) & “.mdb”

    Set fso = New FileSystemObject
    fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
    Set fso = Nothing

    Regards,

    ReplyDelete
  12. strBatchFile variable have the Location Address of the Batch File: bkup.bat. This file must be in the same location of the Database. The Main Program you must save in the Standard Module not in the Form Module.

    ReplyDelete
  13. ok how can i refer it with "Call" ?

    In above you said
    """"

    3.Add the following line of Code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other Form that opens immediately after loading the Database.

    SysBackup """" How ??????

    ReplyDelete
  14. You can call the program as below:

    Call Sysbackup()

    ReplyDelete
  15. sory dear friend in first may i chat with you in google ?
    i sent an invate to you ......

    I have a question
    i want send first page of report to one report and
    2nd to end pages to another report
    how can i do that ?

    thanks

    and one more question :
    i have a database on server , i want to allow some of users to read and some of others write database
    in location i did security for domain mention users but
    who has allow to write in server folder , can delete database
    how can i security more that user could n't delete source but can wirte in database table ?????

    thanks more

    ReplyDelete
  16. A. First on the Report issue:
    You cannot split the same Report in two unless you export the Report into PDF Format (If you have Adobe PDF Writer installed) and split the pages through Documents Menu Options. 

    If you are organizing your Report-Data in a certain order using Queries then you can solve your problem with two copies of the same Query(with different names: say Query1 and Query2) and two copies of the same Report (say Report1 and Report2). 

    Query1-SQL : 

    SELECT TOP 50 Employees.*
    FROM Employees
    ORDER BY Employees.EmployeeID;
    Design Report1 on this Query to Print the first 50 Employees List on the first page of the Report.

    Query 2 SQL : 

    SELECT Employees.*
    FROM Employees
    WHERE (((Employees.EmployeeID) Not In (select EmployeeID from Query24)))
    ORDER BY Employees.EmployeeID;
     
    In Query2 we are using Query1 Employee Codes as criteria to exclude them from Report2. 

    You can design two different Reports on both Queries and sent to different destinations. 

    Instead of the sample number 50, which I have used in the above example you may select a convenient number to fit on the first page of your Report depending on the design of your Reports. 

    B. Server side access rights are enforced by Network Administrators. If the database is read only then users cannot write anything into it and save it. Access rights on individual database objects are implemented in the same way assigned on individual files on Server.  

    Normally the Network Server is backed-up on daily, Weekly, Monthly basis by the Network Backup Operators. If your database is lost you can ask the Network Administrators to restore from the latest Backups. It is better if you implement a daily backup method as explained in my Article:Database Daily Backup

    Regards,

    ReplyDelete
  17. hi dear friend
    thanks for your help

    ReplyDelete
  18. I accidently foun this code, which is very neat but I am having problems getting it to work it, as it is displaying error "File Not Found". Any ideas?

    ReplyDelete
  19. Check whether the Batch File is present in the Database location.

    ReplyDelete
  20. Hi, thanks for the reply

    Managed to workout the problem. There was a end if missing, and also had to remove the spaces in the path from your code example. Am just wondering will this always overwrite the previous backup version?

    Thanks

    ReplyDelete
  21. Yes, it will overwrite the file every time.

    ReplyDelete
  22. hi again how are u
    May you take a template of this backup ( sample database) for download here ???
    we need to see in sample how you did

    Best regards

    ReplyDelete
  23. hi there
    how are you today
    i did download your Demo and run it.
    back up ok in your file but have this problem error "Undefined function 'Environ' in expression "

    But in My database on startup this Code , have an error in 'j'
    it said , varibale not defined

    Private Sub Form_Timer()
    j = j + 1
    If j > 2 Then
    Me.TimerInterval = 0
    SysBackup
    End If

    End Sub


    please help

    ReplyDelete
  24. You must add a line Dim J at the top of the Form Code Module below the line Option Compare Database as shown below:

    Option Compare Database
    Dim j

    to correct the 'variable not defined' Error.

    You must attach the Library File: Microsoft Visual Basic for Applications Extensibility 5.3 to your Database to rectify the Undefined 'Environ()' Function Error. Do the following:

    1. Display the Visual Basic Editing window (Alt+F11).
    2. Select References from Tools Menu.
    3. Look for the name Microsoft Visual Basic for Applications Extensibility 5.3 and put a check mark to select it.
    4. Click OK to complete.

    Regards,

    ReplyDelete
  25. thanks it is ok

    i have more question master please .............LOL

    think we have a report , and we want fix 10 or more row in report
    therefor if data of some range has 2 row , we could see 8 row blank fixed in below of it .... clear i say , we want see always 10 row record , HOWEVER it is blank like excel ....

    could we do ???
    example : report has signing row in below and we must have 10 row in report and signing row in below of them .

    thanks

    ReplyDelete
  26. sory you didnt say about this error i said

    “Undefined function ‘Environ’ in expression ”

    ReplyDelete
  27. one question :
    every open database it is run , what matter ???
    only if date is today code is stop but if you change date of bkupdate table is yesterday , the code is run evey each open ???

    ReplyDelete
  28. Yes, already did mention about the 'Environ' Error. You can correct that error by attaching the Library File: 'Microsoft Visual Basic for Applications Extensibility 5.3' to your database. The procedure is explained earlier, please refer my earlier reply.

    ReplyDelete
  29. The Daily backup will run only once when you open the database for the first time during the day. Today's date is updated in the parameter table after the backup. This will prevent taking backups every time you open the database during the day.

    ReplyDelete
  30. Regarding the Question of 10 lines Report, I think with few blank records in a Table, with the same structure of your Report Query, and a Union Query with the control of a VBA Program we can create your Report the way you want.   Copy the Table,Query and Report into a separate database, Zip the database with Winzip and forward it to me by mail : aprpillai@msaccesstips.com

    ReplyDelete
  31. hi dear friend
    at first do you have yahoo messenge ID or Gmail ID for Chat ?
    I have a problem and i hope you could help me .
    in my access database , i have a form named "ReportDateRange" and it has 2 box , "Start Date " and "Finish Date" , all of my report did set to this Date Range , i did set those boxes to Date() by Default , but at first boot , i have to open and save mention form to work .
    could i find a way that without open it , to set date Rage
    Note : this way could help the users for setting once date range for all report....
    Beacuse of this problem i have to open mention form via Autoexec at first open of Main form at the same time ...

    thanks Before

    ReplyDelete
  32. Please send me e-mail to: aprpillai@msaccesstips.com explaining your issues.

    Regards,

    ReplyDelete
  33. It is better if you create a Parameter Form linked to a Table with two fields: StartDate and EndDate with only one record in the Table. You may create Option Group control on this Form to run different Reports using the StartDate and EndDate Field Values as criteria parameter.

    Once you set the date values into the Table Fields through the Parameter Form it will remain intact for all your purposes till you change it.

    Regards,

    ReplyDelete
  34. hi dear friend
    i hope you have a good day today
    this solution is good but i made my all report by setting this parameter and if i want to change it , it is very hard to find all records to change ....
    so , if i could Run this form , mention form i mean from start , if i could to open automaticaly and push save automaticaly with vb code it will be solved , this is My form code ......
    i have a command button in this form with Preview named contain this code on it

    Private Sub Preview_Click()
    If IsNull([Beginning Entry Date]) Or IsNull([Ending Entry Date]) Then
    MsgBox "You must enter both beginning and ending dates."
    DoCmd.GoToControl "Beginning Entry Date"
    Else
    If [Beginning Entry Date] > [Ending Entry Date] Then
    MsgBox "Please Retype Finish Date : Must be Greater or Equal Than Current Date"
    DoCmd.GoToControl "Beginning Entry Date"
    Else
    Me.Visible = False
    End If
    End If
    End Sub

    i did call this function with this sample
    Call Preview_Click
    but is not working
    do u know any solution ??? for run this command via autoexec macro?

    thanks

    ReplyDelete
  35. You can write the Code in a Standard Module as a Public Function and call it from the Command Button Click Event Procedure with the Form name as Parameter. You may Copy and Paste the following Code into a Standard Module:



    Public Function Pre_view(ByVal strfrm As String)
    Dim frm As Form

    Set frm = Forms(strfrm)
    If IsNull(frm![Beginning Entry Date]) Or IsNull(frm![Ending Entry Date]) Then

    MsgBox "You must enter both beginning and ending dates."

    DoCmd.GoToControl "Beginning Entry Date"
    Else
    If [frm]![Beginning Entry Date] > frm![Ending Entry Date] Then
    MsgBox "Please Retype Finish Date : Must be Greater or Equal Than Current Date"
    DoCmd.GoToControl "Beginning Entry Date"
    Else
    frm.Visible = False
    End If
    End If
    End Function



    Write the following Code into the Command Button Click Event Procedure:


    Private Sub Preview_Click()
    Pre_view Me.Name
    End Sub


    You can use the Autoexec Macro to Open the Form on startup.

    ReplyDelete
  36. hi dear friend
    i did this solution before but i did again same you said

    after i create on top codes
    i did add open the mention form in startup and add this line to below of it
    RunCode ..... Pre_view
    But it has an error to startup the form via macro general error
    it cannot be run via Autoexec

    thansk Before

    ReplyDelete
  37. in your Code ... , What is "strfrm" ???


    i want to RunCode this Command via Startup what Could i must to do ??

    ReplyDelete
  38. and what is "Me.Name" ??

    ReplyDelete
  39. Me represents the active Form. Active Form Name is passed to the Program as parameter. You can pass the name of the active Form in quotes like Pre_View "myForm" while calling a Program from within the Form VBA Module. If your form's name is myForm.

    ReplyDelete
  40. When you attempt to Run Pre_View() Program from Macro; the Form is not open to validate the Field Values in the Program. That is why the Program is showing error. The Form must be already in open state before we run the program. 

    Create a Macro with the name Autoexec to open your form as soon as you open the Database (with OpenForm Action and type the Form's name in the Form Name Property and save the macro. 

    Copy and Paste the following lines of Code into the VBA Module of the Form: 


    Private Sub Form_Load()
    Pre_view Me.Name
    End Sub
     

    ReplyDelete
  41. strfrm is a Variable defined in the Program as parameter to receive the Form's Name into the Program so that we can use it to refer to the controls (like Beginning Entry date to validate its contents.

    When we call the Program like Pre_View "myForm" the form name myForm will be passed into the Variable strfrm in the main program and we use that information for further actions. I hope it is clear now.

    ReplyDelete
  42. well written blog. Im glad that I could find more info on this. thanks

    ReplyDelete
  43. Private Sub cmd1_Click()
    Dim str As String
    Dim I As Integer
    I = w1dia
    If rs.State = 1 Then rs.Close
    str = "Select * from AXLEMASTERS where W1Dia" ' w1dia Then
    rs.Find "W1Dia = " & Txt1.Text
    If rs.EOF Then
    cr1.ReportFileName = App.Path & "\lowdia.rpt"
    cr1.DataFiles(0) = App.Path & "\LocoShed.mdb"
    cr1.Action = 1
    Else
    Do Until rs.EOF
    MsgBox "Record not found"
    rs.MoveNext
    Loop

    End If
    End If
    End Sub

    the above said coding used but i unable to receive the below or less than data on report please any one help me

    ReplyDelete
  44. I presume that you have opened the Recordsetclone in a different eventprocedure!

    Private Sub cmd1_Click()
    Dim str As String
    Dim I As Integer
    I = w1dia
    If rs.State = 1 Then
    rs.Close
    str = "Select * from AXLEMASTERS where W1Dia = " & w1dia & ";"
    rs.Find "W1Dia = " & Txt1.Text
    If not rs.NoMatch Then ' the record found
    'do what you want to do here when the record is found
    cr1.ReportFileName = App.Path & "\lowdia.rpt"
    cr1.DataFiles(0) = App.Path & "\LocoShed.mdb"
    cr1.Action = 1
    Else
    MsgBox "Record not found"
    End If
    End If
    rs.close
    End Sub

    If it gives error again note down the Error Message with Error Number and given feed back.

    ReplyDelete
  45. lol a couple of the reviews bloggers write are just silly and unrelated, sometimes i wonder whether they at all read the post before writing or whether they merely look at the subject of the post and write the very first thought that comes to their minds. But it is nice to find a fresh commentary every now and then in contrast to the exact same, traditional blog garbage which I oftentimes notice on the blogs. Cheers

    ReplyDelete
  46. hi dear friend

    after administrator is working
    i want to show the users a custom text like
    "admin is working please try later "

    Could i do ???

    thanks before

    farzad From Iran

    ReplyDelete
  47. hi dear friend
    in first what was this pop up in your site openning ???!!!

    Question : how can i get autonumber in a query ?
    i Tryed with Count function but is not working well
    if query have some result such as below mention

    ID TagNo Description
    1 A .......
    4 B .......
    9 C .......

    i want had

    Autonumber ID TagNo Description
    1 1 A .......
    2 4 B .......
    3 9 C .......

    Every time i Run this Query by other Result i want had Autonumber for new Result

    Thanks Before

    ReplyDelete
  48. HI
    I have one more Question please pay Attention to me

    what permission i must to active for user that could Run a "Make Table Query"
    for mention you know that before Run this Queries we have no any table for permission and every time we Run a make table Queries this table will delete and recreated

    Thanks for your time

    ReplyDelete
  49. Open the Make-Table Query in Design View
    Display the Query Property Sheet
    Change the Run Permissions Property Value from User's to Owner's
    Save the Query with the change.

    ReplyDelete
  50. The Popup above the default theme menu is a new Menu Bar. You can select any Article directly from this pop-up Menu.

    Regards,

    ReplyDelete
  51. Thanks, found the article on google...

    Nice Post mate...

    ReplyDelete
  52. hi dear,
    i have downloaded your demo but when i run the demo i always getting error Undefined function 'Environ' in expression. i have attached Microsoft Visual Basic for Application Extensibility 5.3 as you suggested in the old post.
    please help to solve this problem

    Regards,

    ReplyDelete
  53. Hi,
    There were some errors in the code. I have corrected them now. Please download the Demo Database again and try it out.

    Sorry for the inconveniece.

    Regards,
    a.p.r.pillai

    ReplyDelete
  54. Hi Admin,

    I was able to replicate this code into my own by defining a new field for BackUp Path. The code below use to work on our network drive but our network team recently made changes to the network drive (not sure what they did) but this is affecting the daily back up I have replicated. Is there any alternative for this? Please see code below:

    [QUOTE]

    Public Function SysBackup()

    Dim dbPathName, j As Long, t As Date
    Dim bckupPath
    Dim bkupdate, strBatchFlle As String, qot As String

    On Error GoTo sysBackup_Err

    qot = Chr$(34)
    bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)

    If bkupdate = Date Or bkupdate = 0 Then
    Exit Function
    End If

    MsgBox "Daily Backup Procedure initiated, please wait...OK."

    bckupPath = DLookup("BkupFolderPath", "Bkup_ctrl")
    dbPathName = DLookup("dbLink", "Bkup_ctrl")
    j = InStrRev(dbPathName, "\")

    If j > 0 Then
    strBatchFlle = Left(dbPathName, j)
    strBatchFile = strBatchFlle & "bakup.bat"
    Open strBatchFile For Output As #1
    Print #1, "@Echo off"
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : " & dbPathName
    Print #1, "Echo Daily Backup to "; bckupPath; ""
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : "
    Print #1, "Echo :Please wait... "
    Print #1, "Echo : "
    Print #1, "Copy " & qot & dbPathName & qot & " " & qot & bckupPath & qot
    'add lines here for Back-end database or for other Files
    Close #1

    'Copy file
    Call Shell(strBatchFile, vbNormalFocus)
    t = Timer
    Do While Timer <= t + 10
    DoEvents 'wait for 10 seconds
    Loop

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('USERNAME'), Bkup_Ctrl.UserStamp = CurrentUser(), Bkup_Ctrl.TimeStamp = Now() ;"
    DoCmd.SetWarnings True
    MsgBox "Daily Backup Done Successfully."
    'Kill strBatchFile
    End If

    sysBackup_Exit:
    Exit Function

    sysBackup_Err:
    MsgBox Err.Description, , "sysBackup()"
    Resume sysBackup_Exit
    End Function

    [\QUOTE]

    ReplyDelete
  55. Probably your Network Drive mapping got changed. If you are backing up your database from your local drive to the Network Drive then you may face problems. You can check the current Network Drive mapping through Windows Explorer. The Drive mapping will show something like the example given below, along with other local Drives:

    myFolder (\\ServerName) (T:)

    Earlier, if your Drive letter changed for the same network path \\Servername\myFolder then you can ask the Network Administrator to map the Server Location \\Servername\myFolder to your old Drive Letter.

    If you are backing up the Database from Server to local drive then create the Batch File on Server Drive, where your database is, and specify only Database Name alone in the Source Path part in the Copy command. When the batch file is run it will take the database name from the batch file path by default.

    ReplyDelete
  56. Sorry Admin, my real problem is this. With the above code and changes made to our network drive, it is no longer working when the database was located in the network drive but if it is on the local drive, it is running. The error I am getting is :

    "Invalid procedure call or argument"

    The code above works if the database was in local drive but not in network drive. Any alternatives you can suggest?

    ReplyDelete
  57. Always save the Batch File on local drive. When Database is on Network Drive take backup on Local Drive. Specify the Source and Target Path in the Copy DOS Command line correctly.

    When the Database or the Backup file is on Network Drive it will be included in the Daily Network backup by the Network Administrators. If something happens to your database you can always request them to restore the database from the latest Network backup Tape.

    If you can post the modified version of the code here, I can check whether every thing with the code is ok or not.

    Check whether you have Windows Network or some other Network Software. Network Administrators can give you this information.

    ReplyDelete
  58. Hi Admin,

    Our network admininstrators does not have a daily back up but I think it is weekly. I need an alternate code that will work on our network drive as the below code works on our local drive.

    PLease see below code. I have a table named Bkup_ctrl where I look up the link to database and the back up path. I am using a splitted database Front end and back end.


    Public Function SysBackup()

    Dim dbPathName, j As Long, t As Date
    Dim bckupPath
    Dim bkupdate, strBatchFlle As String, qot As String

    On Error GoTo sysBackup_Err

    qot = Chr$(34)
    bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)

    If bkupdate = DATE Or bkupdate = 0 Then
    Exit Function
    End If

    MsgBox "Daily Backup Procedure initiated, please wait...OK."

    bckupPath = DLookup("BkupFolderPath", "Bkup_ctrl")
    dbPathName = DLookup("dbLink", "Bkup_ctrl")
    j = InStrRev(dbPathName, "\")

    If j > 0 Then
    strBatchFlle = Left(dbPathName, j)
    strBatchFile = strBatchFlle & "bakup.bat"
    Open strBatchFile For Output As #1
    Print #1, "@Echo off"
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : " & dbPathName
    Print #1, "Echo Daily Backup to "; bckupPath; ""
    Print #1, "Echo :------------------------- "
    Print #1, "Echo : "
    Print #1, "Echo :Please wait... "
    Print #1, "Echo : "
    Print #1, "Copy " & qot & dbPathName & qot & " " & qot & bckupPath & qot
    'add lines here for Back-end database or for other Files
    Close #1

    'Copy file
    Call Shell(strBatchFile, vbNormalFocus)
    t = Timer
    Do While Timer <= t + 20
    DoEvents 'wait for 10 seconds
    Loop

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('USERNAME'), Bkup_Ctrl.UserStamp = CurrentUser(), Bkup_Ctrl.TimeStamp = Now() ;"
    DoCmd.SetWarnings True
    MsgBox "Daily Backup Done Successfully."
    'Kill strBatchFile
    End If

    sysBackup_Exit:
    Exit Function

    sysBackup_Err:
    MsgBox Err.Description, , "sysBackup()"
    Resume sysBackup_Exit
    End Function

    ReplyDelete
  59. Please provide the following details:


    Your Network Drive mapping Address, where the backup copy is targetted. It will look like: SharedDocs (\\ServerName\FolderName) (T:). Use Windows Explorer to find out.

    Local Drive Path where the Back-end and Front-end databases are located.
    The following field values from the Backup_Ctrl Table:




    BkupFolderPath = ?

    dblink = ?

    ReplyDelete
  60. Hi,

    I downloaded the demo that works wonderful. When I copy and paste all the codes in the module, and also on load and on timer, there is no action. My access file is accdb, Access 2007. I'm really confused.

    ReplyDelete
  61. Hi,

    what is "object required" error? When I run your demo code, it comes up.

    ReplyDelete
  62. Hi,
    Disable the following line from running by putting single quote in the first column as shown below:

    'On Error GoTo sysBackup_Err

    When the error message pops up select the Debug option to stop the Code on the error line and note it down. Please let me know where exactly the code runs into error. Perhaps you may need to attach the DAO reference library file.

    ReplyDelete
  63. Check whether the On Load and On Timer Event properties are loaded with the [Event Procedure] values. If not select it from the drop-down control on the property.

    ReplyDelete
  64. Hi, I have found your tutorial very interesting by the way I want it to backup automatically on a schedule time like every 7h PM and to check that the .mdb file isn't open before doing it. Any help like the code or the file itself would be greatly appreciate.
    Thanks

    ReplyDelete
  65. I think, the best and easy approach is to use Windows Backup/Restore Utility (Start-->Control Panel-->Backup and Restore). You can schedule Automatic Backups, save file(s) on external storage devices, on a specific time on Daily/Weekly/Monthly frequency. You can select specific Files/Folders/Drive to backup as well.

    Since, you have gone through the Daily backup method explained on this page, check the automatic email alerts scheduling trick on this post:Automated EMail Alers for setting up time schedule and checking. You must setup programs that runs at specific intervals (say at every minute) and compare the target time with the computer's clock to determine whether the time has reached to transfer control to the Backup Program.

    Open status of the database can be checked for the presence of a lock file with the same name of the database but with the file extension .ldb (access2003) or .laccdb (access2007). Check this link: Who is Online to learn some trick with this file.

    ReplyDelete

Trending

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards ms-access functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Custom Functions Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts