Anyone have any sample code or know of utilities to backup changed
records while a backup MDB is open and in use? I know you can backup
via windows file system and know of the small risk of yielding a
corrupted copy. I heard about someone who used datetimestamps of
latest update on the record to retrieve new or changed records using
an append query and to write them to an external db. Anyone have code
to do this?

Anyone know of any Access utilities that backups changed database
records while the database is active?

Bob

Bob Alston <bobalston (AT) gmail (DOT) com> wrote in
news:3770d9dc-9d86-4056-9422-267ce2979fe7 (AT) c8g2000vbv (DOT) googlegroups.com
:

I've posted many, many times in several different forums about the
logic of synchronizing two different databases. I'd have to Google
to find the most descriptive, but basically, it's not that hard:

1. check for new records and add them to the backup.

2. check for records in the backup that aren't in the current copy
-- decide whether to delete them or just mark them deleted.

3. run code to update records in the backup that also exist in the
current data.

The latter I do in DAO code by writing SQL on the fly to update one
column at a time (this is more efficient than writing a big 'ol
honkin' SQL update that sets all fields at once in one go). There
are a few minor complications, such as writing the SET and WHERE
clauses with appropriate delimiters (or not), but that's not that
tough to figure out.

Since these three parts of the process are using Jet/ACE for the
updates, it's perfectly safe on live data.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 3/7/2011 1:49 PM, David-W-Fenton wrote:

My key tables have date/time stamps for creation and for latest update.
I was thinking of vba code to on the fly create a query, one table at
a time, to query any records created or updated since the last run.
That would be the input query. Then there would be either an update
query using that input or maybe vba code.

Can't decide if the process would run much faster if the target backup
database was empty except for the day's additions and changes. That
would minimize the size of the table indexes but not sure if it would be
worth it.

As I recall in reading the post about Wayne gillespie who did something
similar, whose code I was looking for, he ran his process every minute.
That sounds like a lot of overhead to me. Here is the link to what he
said:

http://groups.google.com/group/comp....29ef100ce8eca2

Thanks for any info you can provide on what you posted previously.

Bob

On 3/7/2011 1:49 PM, David-W-Fenton wrote:

I may have found one of the posts to which you refer:

http://www.accessmonster.com/Uwe/For...bles-in-access

That would seem to be very robust and work regardless of any date/time
stamps on the records. However it would seem that it would take a lot
of time to process.

Keeping a backup up to date during the day (without Jet replication)
--------------------------------------------------------------------
My interest is keeping a backup during the day. I have a system working
on a nonprofit client's network that has the databbases on a 4.x Netware
server which does not provide a reliable connection with the windows
users who run various windows and Access 2003. Usually when there is
corruption I can recover, often using the stand alone compact/repair
utility. Last week was the first time I lost a days worthy of data for
one table. That was awful. During the day I saw the new records and
was going to combine them with the prior day's backup at the end of the
day when everyone was off the system.

Hot copy via windows while database open
----------------------------------------
Another thing I just did was implement a hot copy, using xcopy to copy
the back end databases as noon. I know this works 99.x percent of the
time. I have also heard of problems when doing so in the active
application. I experienced one of those when using my app while the
copy was underway - as a test. I later added a persistent connection to
the database (two back end databases and I had missed doing this for one
of them) and subsequently could not recreate the error. I don't have
the exact error (don't I sound just like a user) but it was one of not
being able to open the file - the back end MDB. so I think I have that
licked.

If anyone has experience doing hot copy via windows, with the database
open and has experienced problems from doing so, I would be interested
in hearing from you.

Bob

BobAlston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:il3grc$743$1 (AT) news (DOT) eternal-september.org:

That's correct. But I sometimes use date/time stamps to restrict the
updates (all the main data tables in all my apps have Updated and
UpdatedBy fields).

It doesn't. That's because it's running a batch update on each
column. It's also updating only the records that have changed,
something that would not be the case with an omnibus update that
updated all fields.

[]

I would never do that. How would you tell if it failed?

Just this afternoon, I'm about to implement a backup in an app that
periodically imports data from an external source (and runs just the
kind of code described in the post above), and in this case, I
intend to just create a new backup database each time. I'm going to
use one of three methods:

1. automate another instance of Access and use SysCommand to make a
backup of the tables (I've never been able to make this work in the
past, unfortunately, but thought I'd give it a try, as it's a few
lines of code).

2. use TransferDatabase to export all the tables to an empty backup
database (this has to be done with automation, since I'm running it
from the front end, and TransferDatabase allows you to specify an
external database only in one of its arguments).

3. append all the data to an empty backup database created from a
template. This is the easiest, but I'd expect the least efficient;
it also has to be done in the correct order to honor RI, assuming I
include RI in the empty template database).

If you're creating a backup, as opposed to synchronizing two
databases, I'd think one of these methods would be best. You'd only
synch if you needed to maintain the backup in a persistent single
file that always had the same name.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote in
news:Xns9EA387AFD6C29f99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.99:

I just implemented my backup using #1, which I got to work without
the slightest difficulties (I don't know why I'd had problems in the
past). The code is posted after my signature (the SaveAsText 6 trick
to back up only tables is something Lyle Fairfield pointed out years
ago). The only thing I might change in the code is to replace the
Dir() check for an folder with a check using the File System Object
(since the code below won't work for an empty folder), but I didn't
want to add any outside dependencies.

I've put in error checking to recover from a MkDir on a folder that
already exists (if you use Dir() on an empty folder), but also
included a commented-out line using the File System Object. The way
I use the FSO is with a function that uses late binding, and I call
it "FSO". I've posted it after the CreateBackup() function.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Public Function CreateBackup(strMDBName As String, _
strBackupPath As String, _
Optional ysnCompact As Boolean = False) As Boolean
On Error GoTo errHandler
Dim objAccess As Object
Dim strBackupMDB As String
Dim strCompactMDB As String

If Len(Dir(strBackupPath & "\*.*")) = 0 Then
'If Not FSO.FolderExists(strBackupPath) Then
MkDir strBackupPath
End If

Set objAccess = New Access.Application
objAccess.Application.OpenCurrentDatabase strMDBName
strBackupMDB = "Backup" & Format(Now(), "YYYYMMDDhhnnss") & ".mdb"
Debug.Print strBackupPath & "\" & strBackupMDB
objAccess.Application.SaveAsText 6, vbNullString, strBackupPath _
& "\" & strBackupMDB
objAccess.Application.Quit
Set objAccess = Nothing

If ysnCompact Then
strCompactMDB = strBackupPath & "\" & "c_" & strBackupMDB
Name strBackupPath & "\" & strBackupMDB As strCompactMDB
DBEngine.CompactDatabase strCompactMDB, strBackupPath _
& "\" & strBackupMDB
Kill strCompactMDB
End If

CreateBackup = (Len(Dir(strBackupPath & "\" & strBackupMDB)) > 0)

exitRoutine:
If Not (objAccess Is Nothing) Then
On Error Resume Next
objAccess.Application.Quit
On Error GoTo 0
Set objAccess = Nothing
End If
Exit Function

errHandler:
Select Case Err.Number
Case 75 ' Path/File access error
Resume Next ' tried to MkDir a folder that already exists
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in CreateBackup()"
Resume exitRoutine
End Select
End Function

Public Function FSO(Optional bolCleanup As Boolean = False) As
Object On Error GoTo errHandler
Static objFSO As Object

If bolCleanup Then GoTo closeFSO

If objFSO Is Nothing Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
End If

exitRoutine:
Set FSO = objFSO
Exit Function

closeFSO:
If Not (FSO Is Nothing) Then
Set objFSO = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in FSO()"
Resume exitRoutine
End Select
End Function

On 3/9/2011 1:24 PM, David-W-Fenton wrote:

Is it something that you could reasonably do multiple times during the
day, while users were actively using the system?

Thanks

bob

"BobAlston" <bobalston9 (AT) yahoo (DOT) com> wrote

Lyle, are you lurking? Has anybody heard from Lyle recently?

Larry

BobAlston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:il8uea$j09$1 (AT) news (DOT) eternal-september.org:

In regard to the first question, I used it in production at the
client yesterday afternoon. The computer I was working on was a slow
one, the network is not terribly great. I was tesing an import
process that alters the main data file, so I wanted a backup before
each run of the import on the test data file (so I had a backup to
revert to after each run). The first time the creation of the backup
took a long time (it was a 28MB data file), i.e., 5 or 10 minutes,
but subsequent backups didn't take nearly as much time. I don't know
what the issue was, but I now plan to alter the process to create
the backup as a temporary file in the %temp% folder on the local
computer, do any compact in place in the %temp% folder, and only
then copy it to the final destination for the backup.

As to the second question, in this case I was the only user. I had
intended to check what happens if the file is in use, but I just
haven't gotten around to it.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in
news:8tqd4mFq29U1 (AT) mid (DOT) individual.net:

I think Lyle has either left Access development entirely, or become
so idiosyncratic in the kinds of things he does with it that he no
longer has any patience with the concerns of us mere mortals who
still use Access the same way we have for years.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/