Prev: Excel Automation and Vista
Next: Using the Access 2007 run-time with a split database
From: Eef on 5 Jul 2008 06:57
Bob,

Something does not work.

I have now

DoCmd.RunCommand acCmdBackup

But the result is
Error 2046
Not available

Any suggestions?

Eef Houniet


"boblarson" <boblarson(a)discussions.microsoft.com> schreef in bericht
news:2CBA933B-63AA-4E35-BEAA-B535269A4B31(a)microsoft.com...
> Eef:
>
> You should avoid using DoMenuItem code as it has been deprecated from
> Microsoft and is only supposed to be there for backwards compatibility
> (yes,
> I know it comes out in the code the wizards generate and we've been
> fighting
> for them to change that).
>
> Use the DoCmd.RunCommand code instead. If you type that in and type a
> space, intellisense should drop down every command you have available.
> --
> Bob Larson
> Access MVP
> Access World Forums Administrator
> Utter Access VIP
>
> Tutorials at http://www.btabdevelopment.com
>
> __________________________________
>
>
> "Eef" wrote:
>
>>
>> Question A
>> Access 2003 has an option under File (Bestand): Back-up from the
>> database.
>> (or some such translation from Dutch)
>>
>> What would this be as MenuItem?
>> (As the following instruction for Save would be:
>> DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70)
>>
>> Question B
>> Is there a list of those MenuItems?
>>
>> Thanks in advance
>> Eef Houniet
>>
>> --
>> Eef
>>
>>
>>


From: Ken Sheridan on 5 Jul 2008 07:55
Eef:

What is it you are trying to do? Do you want a means of regularly backing
up the data from within your database? If so, I'd advise that you firstly
split the database into front and back ends if its not already split. the
built in database splitter wizard will do this for you, creating a new back
end file with a '_be' suffix and replacing the local tables in your current
file with links to the back end. This means you can back up the data in the
back end separately. It also means you can distribute copies of the front
end to other users in a multi-user environment and link them to a single back
end file on a server. You should keep a 'master copy' of the front end
somewhere safe.

To back up the data from within the front end you can use the following
module. Just paste the code into a new blank module and save it as something
like mdlBackUp (the name isn't important so long as it does not conflict with
the name of a function, procedure etc).

''''module starts''''
Option Compare Database
Option Explicit

Declare Function CopyFile& Lib "kernel32" Alias "CopyFileA" (ByVal _
lpExistingFilename As String, ByVal lbNewFileName As String, ByVal _
bFailIfExists As Long)


Public Sub MakeFileCopy(strExistingFile As String, _
strNewfile As String, _
blnDoNotOverWrite As Boolean, _
Optional blnShowMessage As Boolean = False)


Dim strMessage As String

strExistingFile = strExistingFile
strNewfile = strNewfile

If CopyFile(strExistingFile, strNewfile, blnDoNotOverWrite) = 1 Then
strMessage = "File successfully copied."
Else
strMessage = "File copy failed."
End If

If blnShowMessage Then
MsgBox strMessage, vbInformation, "Copy File"
End If

End Sub


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

Const FILEINUSE = 3356
Dim strMessage As String
Dim strBackUpTemp 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
' make temporary copy of backend file and then delete current
back up
strBackUpTemp = Left(strBackUp, InStr(strBackUp, ".")) & "bak"
MakeFileCopy strBackUp, strBackUpTemp, False
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
Application.CompactRepair strBackEnd, strBackUp
If Err.Number = FILEINUSE Then
' file in use by current user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" You may have a table in it open."
MsgBox strMessage
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Exit Sub
Else
On Error GoTo 0
' ensure back up file created
If Dir(strBackUp) = Mid(strBackUp, InStrRev(strBackUp, "\") + 1)
Then
strMessage = "Back up successfully carried out."
' delete temporary copy of back up file if exists
On Error Resume Next
Kill strBackUpTemp
On Error GoTo 0
Else
strMessage = "Back up failed."
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End If
MsgBox strMessage, vbInformation, "Back up"
End If
Case FILEINUSE
' file in use - inform user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" It may be in use by another user."
MsgBox strMessage
' rename temporary copy of back up file,
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Case Else
' unknown error - inform user
MsgBox Err.Description, vbExclamation, "Error"
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End Select

End Sub
''''module ends''''

To back up the back end file you call the BackUp procedure like so (as one
line of code, it will probably be split over two lines in your newsreader)

BackUp "F:\MyFiles\MyDatabases\MyDatabase.mdb",
"F:\MyFiles\MyDatabases\MyBackUps\MyDatabase.mdb"

This will make a copy of your database in the MyBackUps subfolder. The
subfolder must already exist. Alternatively you could back up to the same
folder as that in which the back end file is located, renaming the file to
MyDatabase_bak.mdb for instance.

Ken Sheridan
Stafford, England

"Eef" wrote:

> Bob,
>
> Something does not work.
>
> I have now
>
> DoCmd.RunCommand acCmdBackup
>
> But the result is
> Error 2046
> Not available
>
> Any suggestions?
>
> Eef Houniet
>
>
> "boblarson" <boblarson(a)discussions.microsoft.com> schreef in bericht
> news:2CBA933B-63AA-4E35-BEAA-B535269A4B31(a)microsoft.com...
> > Eef:
> >
> > You should avoid using DoMenuItem code as it has been deprecated from
> > Microsoft and is only supposed to be there for backwards compatibility
> > (yes,
> > I know it comes out in the code the wizards generate and we've been
> > fighting
> > for them to change that).
> >
> > Use the DoCmd.RunCommand code instead. If you type that in and type a
> > space, intellisense should drop down every command you have available.
> > --
> > Bob Larson
> > Access MVP
> > Access World Forums Administrator
> > Utter Access VIP
> >
> > Tutorials at http://www.btabdevelopment.com
> >
> > __________________________________
> >
> >
> > "Eef" wrote:
> >
> >>
> >> Question A
> >> Access 2003 has an option under File (Bestand): Back-up from the
> >> database.
> >> (or some such translation from Dutch)
> >>
> >> What would this be as MenuItem?
> >> (As the following instruction for Save would be:
> >> DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70)
> >>
> >> Question B
> >> Is there a list of those MenuItems?
> >>
> >> Thanks in advance
> >> Eef Houniet
> >>
> >> --
> >> Eef
> >>
> >>
> >>
>
>
>

 | 
Pages: 1
Prev: Excel Automation and Vista
Next: Using the Access 2007 run-time with a split database