Backing up open database

Hi All,

Two part question: can an open database be backed up (by the user who has it 
open) and if so, are there any issues associated with doing so?

I have an Access 2000 database through which we crunch payroll data from a 
Progress database for the purpose of printing information not stored in the 
Progress database on check stubs.  The Access database is not set up such 
that the users open it exclusively however, I have code in place that 
prevents a second user from opening the database if someone's already in. 
As part of the payroll processing, I have the payroll clerks notify me when 
they are ready to use the database; before they get in, I create a backup 
then give them the green light.

I'd like this to work such that the clerk opens the database, clicks a 
button to create the backup, then proceeds with their normal duties.  But, I 
don't want to run into issues, if they exist, with backing up a database 
that is open.  I know that I can write a script for them to use to back up 
the database before they access it, but I think it would be cleaner if the 
function lived in the database.

Any ideas, tips, suggestions are appreciated.

Thanks & Ciao 


0
Tony
12/20/2007 10:13:42 PM
access 16762 articles. 3 followers. Follow

3 Replies
913 Views

Similar Articles

[PageSpeed] 51

On Thu, 20 Dec 2007 16:13:42 -0600, "Tony"
<|toxendine@hoopercorp.com|> wrote:

I'm assuming you have split the database in frontend and backend,
following best practices. You now want to backup the backend while it
is in use.
This is technically possible, and will produce a good copy 99% of the
time. You have to decide if that's good enough for you.

Personally I would give them another shortcut on their desktop
pointing to a small vbscript file to make a backup. This script would
also check if the db was in use (by looking for an LDB). They would
run this before starting the Access application.

-Tom.



>Hi All,
>
>Two part question: can an open database be backed up (by the user who has it 
>open) and if so, are there any issues associated with doing so?
>
>I have an Access 2000 database through which we crunch payroll data from a 
>Progress database for the purpose of printing information not stored in the 
>Progress database on check stubs.  The Access database is not set up such 
>that the users open it exclusively however, I have code in place that 
>prevents a second user from opening the database if someone's already in. 
>As part of the payroll processing, I have the payroll clerks notify me when 
>they are ready to use the database; before they get in, I create a backup 
>then give them the green light.
>
>I'd like this to work such that the clerk opens the database, clicks a 
>button to create the backup, then proceeds with their normal duties.  But, I 
>don't want to run into issues, if they exist, with backing up a database 
>that is open.  I know that I can write a script for them to use to back up 
>the database before they access it, but I think it would be cleaner if the 
>function lived in the database.
>
>Any ideas, tips, suggestions are appreciated.
>
>Thanks & Ciao 
>
0
Tom
12/21/2007 1:03:30 AM
If the database is split into front and back ends, which is advisable even in 
a single user environment, the back end containing the data can be backed up 
provided that the current or any other user has no tables from it currently 
open.  There is no point backing up the current front end as a 'master' copy 
of this would be held safely by the database administrator (presumably you).

To back up the back end from the front end add a procedure such as this to a 
standard module in the database.  Make sure you save the module with a 
different name to the procedure e.g. mdlBackUp.

Public Sub BackUp(strBackEnd As String, strBackUp As String)

    Const FILEINUSE = 3356
    Dim strMessage As String
    
    ' if back up file exists get user confirmation
    ' to delete it
    If Dir(strBackUp) <> "" Then
        strMessage = "Delete existing file " & strBackUp & "?"
        If MsgBox(strMessage, vbQuestion + vbYesNo, "Confirm") = vbNo Then
            strMessage = "Back up aborted."
            MsgBox strMessage, vbInformation, "Back up"
            Exit Sub
        Else
            Kill strBackUp
        End If
    End If
    
    On Error Resume Next
    ' attempt to open backend exclusively
    OpenDatabase Name:=strBackEnd, Options:=True

    Select Case Err.Number
        Case 0
        ' no error so proceed
        On Error Goto 0
        Application.CompactRepair strBackEnd, strBackUp
        ' ensure back up file created
        If Dir(strBackUp) = Mid(strBackUp, InStrRev(strBackUp, "\") + 1) Then
            strMessage = "Back up successfully carried out."
        Else
            strMessage = "Back up failed."
        End If
        MsgBox strMessage, vbInformation, "Back up"
        Case FILEINUSE
        ' file in use - inform user
        strMessage = "The file " & strBackEnd & _
            " is currently unavailable. " & _
            " It may be in use by another user" & _
            " or you may have a table in it open."
        MsgBox strMessage
        Case Else
        ' unknown error - inform user
        MsgBox Err.Description, vbExclamation, "Error"
    End Select
    
End Sub

Call the procedure from somewhere in the database passing the path to the 
back end file and that to the back up file you want to create, e.g. (as a 
single line of code)

BackUp "F:\SomeFolder\SomeSubFolder\SomeDatabase.mdb", 
"F:\SomeFolder\SomeSubFolder\SomeDatabase_bk.mdb"

If you want to overwrite the current back up every time you can hard code 
the paths, or better still store them in a table and get them from there 
using the DLookup function.  If you want the user to be able to name the back 
up file each time you can open a common.  You'll find various examples 
online, but I usually use Bill Wilson's class module from:


http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=22415&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

"Tony" wrote:

> Hi All,
> 
> Two part question: can an open database be backed up (by the user who has it 
> open) and if so, are there any issues associated with doing so?
> 
> I have an Access 2000 database through which we crunch payroll data from a 
> Progress database for the purpose of printing information not stored in the 
> Progress database on check stubs.  The Access database is not set up such 
> that the users open it exclusively however, I have code in place that 
> prevents a second user from opening the database if someone's already in. 
> As part of the payroll processing, I have the payroll clerks notify me when 
> they are ready to use the database; before they get in, I create a backup 
> then give them the green light.
> 
> I'd like this to work such that the clerk opens the database, clicks a 
> button to create the backup, then proceeds with their normal duties.  But, I 
> don't want to run into issues, if they exist, with backing up a database 
> that is open.  I know that I can write a script for them to use to back up 
> the database before they access it, but I think it would be cleaner if the 
> function lived in the database.
> 
> Any ideas, tips, suggestions are appreciated.
> 
> Thanks & Ciao 
> 
> 
>

0
Utf
12/21/2007 6:25:01 PM
Tom,

Thanks for the input.  I'm still undecided as to how to do this since 99% of 
the time may or may not work; I don't get to make the call on that one.  I 
appreciate the feedback.

Tony

"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message 
news:e14mm3hti6tvid11m7btvsvvpcu7pbhfea@4ax.com...
> On Thu, 20 Dec 2007 16:13:42 -0600, "Tony"
> <|toxendine@hoopercorp.com|> wrote:
>
> I'm assuming you have split the database in frontend and backend,
> following best practices. You now want to backup the backend while it
> is in use.
> This is technically possible, and will produce a good copy 99% of the
> time. You have to decide if that's good enough for you.
>
> Personally I would give them another shortcut on their desktop
> pointing to a small vbscript file to make a backup. This script would
> also check if the db was in use (by looking for an LDB). They would
> run this before starting the Access application.
>
> -Tom.
>
>
>
>>Hi All,
>>
>>Two part question: can an open database be backed up (by the user who has 
>>it
>>open) and if so, are there any issues associated with doing so?
>>
>>I have an Access 2000 database through which we crunch payroll data from a
>>Progress database for the purpose of printing information not stored in 
>>the
>>Progress database on check stubs.  The Access database is not set up such
>>that the users open it exclusively however, I have code in place that
>>prevents a second user from opening the database if someone's already in.
>>As part of the payroll processing, I have the payroll clerks notify me 
>>when
>>they are ready to use the database; before they get in, I create a backup
>>then give them the green light.
>>
>>I'd like this to work such that the clerk opens the database, clicks a
>>button to create the backup, then proceeds with their normal duties.  But, 
>>I
>>don't want to run into issues, if they exist, with backing up a database
>>that is open.  I know that I can write a script for them to use to back up
>>the database before they access it, but I think it would be cleaner if the
>>function lived in the database.
>>
>>Any ideas, tips, suggestions are appreciated.
>>
>>Thanks & Ciao
>> 


0
Tony
12/21/2007 10:22:30 PM
Reply:

Similar Artilces:

Exchange 2003
Hi All my incoming e-mails are in queue of Exchange 2003 Front-End Server. I tried to telnet:25 back-end from front-end and received this error message: helo 550 5.2.1 Mail from z.x.y.w refused: spam site. Any ideas? Thanks "Johnny B." <anonymous@discussions.microsoft.com> wrote: >All my incoming e-mails are in queue of Exchange 2003 >Front-End Server. > >I tried to telnet:25 back-end from front-end and received >this error message: > > >helo >550 5.2.1 Mail from z.x.y.w refused: spam site. > >Any ideas? Are you sure you haven'...

How to open a form in Outlook 2010?
Just upgraded to Outlook 2010 and I can't find how to open a form! The MSOffice Excel Outlook 2003 to 2010 spreadsheet says that "Forms | Choose Form" is now "File | Choose Form", but I don't see "Choose Form". Under options, "Custom Forms" I can see my forms, but not how to open them! teaboy;710283 Wrote: > Just upgraded to Outlook 2010 and I can't find how to open a form! The > MSOffice Excel Outlook 2003 to 2010 spreadsheet says that "Forms | Choose > Form" is now "File | Choose Form", but I ...

Is Back-Dating possible?
Last Friday I made many calls, but did not have time to enter those activities into CRM for an existing account. Is it possible to close those activities with Fridayt's date as opposed to today's? Thanks Unfortunately you cannot do that. Only modifications to the database do allow you to make these changes, which ofcourse aren't supported by Microsoft. -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://ronaldlemmen.blogspot.com/ "circulent" wrote: > Last Friday I made many calls, but did not have time to enter those > activities into CRM for an exist...

how do I default outlook to open in the inbox?
Open Microsoft Outlook and select Tools | Options | Other tab | Advanced Options. Setting should be at the top to set which folder should be displayed first. "kazandian" <kazandian@discussions.microsoft.com> wrote in message news:A9C4B1A6-9D9C-49E8-8585-0C6EC43CC74A@microsoft.com... > ...

Money 06 Crashes Opening Calendar
Hi y'all, I recently downloaded Money '06 for WinXP and added all my new accounts. Just yesterday, it started to crash everytime I'd click on the Bills tab. It didn't do this until after I got all my accounts in there. Anyone else had this problem? I don't know if this is related, but Iwas a subscriber to MSN Bill Pay but removed the accounts from Money. Other than that, I can't think of anything else that would affect it...hmmm. JazzFan In microsoft.public.money, JazzFan wrote: > > >I recently downloaded Money '06 for WinXP and added all my new &...

Opening Protected EXCEL worksheets to update linked data
I have the following code that should open all EXCEL workbooks in a specified path, and unprotoect any password-protected worksheets to allow for Link Updates, then close the workbook after password protecting it. For some reason, I can't get this code to work -- any ideas? ================================ I put the following code in a general module of a sheet1 of a workbook sub UpdateAllLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet sPath = " C:\Documents and Settings\Shane\My Documents\Harcourt Assessments\password" sName = Dir(sPath & ...

word 07 won't change the open file to the name I just "saved as"
I am new to WOrd 07, but am familiar with 97. If I open a file in and old format and then save it using "save as" and even save it to the new format, why doesn't it change the name of the file I am working on to the new file name? I also get a second word window with the original file opened. Part of the "Save as" is to change the name. You must do this yourself as no one else has the foggiest notion what your desire. "rgw1085" <rgw1085@discussions.microsoft.com> wrote in message news:121620EB-B1B6-4182-B9AE-DFD68BAA5879@microsoft.com... ...

How to change the index/indexer server back to its old name??
Our WSS search server (Search Server Express I think) thinks its server is called X instead of Y. Any attempt to work with the crawling rules results in an error, it thinks the search service is offline, though search service really is running. Is there any way I can get the search server to know that it should look for a server called Y instead of X?? Thank you... ------=_NextPart_0001_8F0E7021 Content-Type: text/plain Content-Transfer-Encoding: 7bit First I would add a host file entry or a DNS entry for the old name to get things back up and working as a temporary measu...

Cannot open Word 2007 doc
I have a Mac G5. Mac OS X 10.4.10. I am receiving documents that are attached to emails that were created using Word 2007. The documents have the extension ".doc" I am running Office for Mac and the Word program is Version 11.3.5. Any advice? Bill If the documents have the extension .doc, you should be able to open them. Files with extension .docx (note the x) would require you to download the beta converter. Can you confirm you didn't misread or mistype and they are really .doc? (no x) Let's eliminate some common issues: --try both double-clicking the doc and us...

Docs opening in new window
Hey all, Is there a way to make publisher documents open using MDI instead of SDI so they all open in a single publisher instance instead of opening new windows, etc... The tools...options menu is devoid of any real customization options and I'm wondering if this is possible, it's quite annoying to go from pub2k to pub2k3 and have every document open into a new window, or every time you open a document and go to close it you close the whole app. instead. This is Publisher 2003 by the way, it used to work in Publisher 2000... Tom While in a state of withdrawal waiting for compo...

Problems opening Outlook 2003
YHi all, I have very recently upgraded office from 2000 to 2003, student and teacher edition. All but Outlook 2003 is working fine, when I open Outlook it says "Your Microsoft Exchange Server is unavailable" - Retry, Work offline or Cancel. I have tried choosing work offline but it then comes up with "Unable to open your default e-mail folders. Your profile is not configured." It then closes :-( Any help much appreciated. Thanks Alex Do you connect to an Exchange Server or are you connecting to an ISP POP3 mail server? "AlexG" <AlexG@discussions.micros...

PC always opens 3 workbooks
Every time I open a spreadsheet I get three workbooks opened. One is the one I clicked on, another is called "book1"and the other is called "personal [read only]". I use a networked pc at work. How do I change the settings so only the document I clicked on is the one that opens? -- Onky Wonky ------------------------------------------------------------------------ Onky Wonky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34542 View this thread: http://www.excelforum.com/showthread.php?threadid=543084 Open excel. Make personal.xls the a...

Macro to open a file open dialog box
Hello. I want to insert a File open dialog box in an existing macro. How do I accomplish this? The file I want to open and run the macro on is a .TXT file. And at the end of my macro, I want to save it in "C:\My Documents" folder as an Excel workbook with the filename (without the .txt extension) plus date plus, of course, the .xls extension. Thanks in advance. something like: Option Explicit Sub testme() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename("Text files, *.txt") If myFileName = False Then...

When I open publisher 2002 it immediately closes.
There appears to be a grey microsoft message box which appears in the center of the screen first, but the program closes so quickly that I cannot read it. Does anyone know how I can fix this problem? All of my other office 2002 applications still work fine. Thanks, Brian Brian wrote: > There appears to be a grey microsoft message box which appears in the > center of the screen first, but the program closes so quickly that I > cannot read it. > > Does anyone know how I can fix this problem? All of my other office > 2002 applications still work fine. > > Thanks, &...

Open dialog not appear
Dear everybod I use excel 97, everytime I click toolbar, the open dialog dosn't appear. But Excel still running when I click/open my document from windows explorer Thank for everyone who have time to help me Dave Peterson posted this There was a virus that affected the File|Save, File|SaveAs and File|Open. http://support.microsoft.com/?kbid=308360 OFF2000: Error Messages Caused by Nimda Virus When You Open or Save Files in Office Programs maybe you got hit. -- Regards Ron de Bruin http://www.rondebruin.nl "wance" <anonymous@discussions.microsoft.com> ...

Unable to open mailbox
We have an old mailbox, from a former employee, that we're trying to get to, and receive the following error: "Cannot start Microsoft Outlook. You do not have permission to log on" His NT account had been disabled, and his mailbox hidden, but it still has 2,000 plus items. I've changed the Primary NT account to mine as adminstrator, created a profile on my machine, with no luck. I've also tried the same with several as well as several other accounts. I can successfully accomplish this task on all the other mailboxes, but it looks like this one doesn't see the Exch...

Can't reply back to a meeting request
If i send a meeting request from Outlook2002 to Outlook 2000. They recieve garbage, but it works with all other 2002 outlook.It looks something like this: PRODID:-//Microsoft Corporation//Outlook 10.0 MIMEDIR//EN VERSION:2.0 METHOD:REQUEST Do not set a reminder when sending from Outlook 2002 to Outlook 2000. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. "steve" <anonymous@discussions.microsoft.com> wrote in message news:0c5b01c3b...

Converting a date from serial back to mm/dd/yyyy hh:mm:ss
I have a spreadsheet that insists on only showing the serial value of the date. For example the cell displays as "6/15/2005 9:58:08 AM" but in the formula bar lists itself as "6/15/2005 9:58:08 AM". If I try to go to Format-->Cells and set the formatting to a different date setting it does not change how it appears and prints inside of the spreadsheet. Anybody ever run into this problem? i think in Excel 2007 there's a DATEVALUE function that would allow you to convert any date formatted as text to be converted back to a serial number reckon by Excel as dates...

Embedded Excel Graph links changing upon updating links/opening
I have multiple charts in an Excel Worksheet (instead of having a tab for each) and I have pasted these charts into a Power Point presentation as "Link - MOE Chart Object". While open, I can update links and everything works properly, however, once I close and reopen the PPT and update the links, all of the charts pasted from one worksheet all show the information from just one of the charts. When looking at the links, they all say "worksheet chart 3". When I have a tab for each, the links work fine, but then I have 100 tabs. Environment: Office 2007 XP...

Email Attachment Won't Open #2
I am trying to send a word document as an attachment, but the receiver can not open the file. Any ideas or suggestions? emery ------------------------------------------------ ~~ Message posted from http://www.OutlookForum.com/ ~~ View and post usenet messages directly from http://www.OutlookForum.com/ >I am trying to send a word document as an attachment, but the receiver >can not open the file. Any ideas or suggestions? Do you know what error messages, if any, they get? Whan Email program are they using and what version is it? -- Brian Tillman Smiths Aerospace 3290 Patterson Av...

How can excel open at the same cell where it was saved?
When I save a spreadsheet/workbook, my cursor is at sheet 1, cell a14. When I open the workbook, my cursor is at sheet 1 but in cell j35. I want it to open with the cursor in cell a14. What mut I do to make this happen? If you save it the way you want, then excel should open it the same way. I'm betting you scrolled after your last save. robsill wrote: > > When I save a spreadsheet/workbook, my cursor is at sheet 1, cell a14. When > I open the workbook, my cursor is at sheet 1 but in cell j35. I want it to > open with the cursor in cell a14. What mut I do to make th...

Links to External Database
I have a number of Excel templates that are linked to external databases. Sometimes I need to adapt the database for specific reporting needs -- when I do this I want to retain the old database for other reasons, but would like to make a copy of the database and have the excel template I am working on point to the new database instead of the old database. I know I can move the database's location and then I can re-assign the database, but is there a way to re-link Excel to the database without having to change the location of the database I am linked to? ...

How can I merge two databases?
I have one database that has First, Last, address, Email, ect. I have another database that has email and our mailing list location. I want to combine the two so that list with only the email and mailing list is replced with the one that has all the contact information. When I am done I should have all the contact information and the mailing list info. Thank you for any help Sorry what I ment to say is I have 2 tables. "RTHart3" wrote: > I have one database that has First, Last, address, Email, ect. I have > another database that has email and our mailing list loc...

A.D.Tejpal's sample databases
Just noticed A.D. has been very busy. Over 100 sample databases at: http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. ...

Pub won't open pub files AFTER the June 10 update???
After I installed all the updates from the Windows update siste Pub won't open pub files ??? Anyone else having this trouble??? Thanks for your time. -- JamesKB JamesKB wrote: > After I installed all the updates from the Windows update siste Pub > won't open pub files ??? Anyone else having this trouble??? > > Thanks for your time. This was caused by Service Pack 2. Microsoft is aware of the problem and has apparently developed a fix for this, but they won't release it until June 30. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org What happene...