error msg "The expression you entered...."

[MikeBart]

hello all, I'm New to Mr.Excel (Access too)

I get this message - The expression you entered has a function name that Microsoft Access can't find."
I copied the VB from a Mr.Excel video about "Auto BackUp" - I'm using 2007-2010
*I need help to resolve this error

Sub BackUp()
Dim dTime As Date
On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValve("12:00:01"))
If Err.Number <> 0 Then Exit Sub
Do Until Time = dTime
DoEvents
Loop
'MsgBox "Time to create a backup"
Dim sFile As String, oDB As DAO.Database
sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".accdb"
If Dir(sFile) <> "" Then Kill sFile
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
oDB.Close
Dim oTD As TableDef
DoCmd.Hourglass True
For Each oTD In CurrentDb.TableDefs
If Left(oTD.Name, 4) <> "Msys" Then 'don't copy Msys tables
    DoCmd.CopyObject sFile, , acTable, oTD.Name
    End If
Next oTD

Dim oQD As QueryDef ' I added this section for queries
For Each oQD In CurrentDb.QueryDefs ' I added this section for queries
If Left(oQD.Name, 4) <> "Msys" Then 'don't copy Msys querys ' I added this section for queries
    DoCmd.CopyObject sFile, , acQuery, oQD.Name ' I added this section for queries
    End If ' I added this section for queries
Next oQD ' I added this section for queries

DoCmd.Hourglass False
MsgBox "Back up is stored in the same folder"End Sub

Function RunSub() ' I followed the instructions in the video to add this into existing autoexec macro
BackUp
End Function

[\code]

 

[xenou]

Time + TimeValve("12:00:01"))

I don't think Time is an MSAccess function. Or TimeValve.

 

[stumac]

Think it should be:

dTime = InputBox("Create a backup at", , Time + TimeVal[B][COLOR=#FF0000]u[/COLOR][/B]e("12:00:01"))

 

[Micron]

I don't think Time is an MSAccess function. Or TimeValve.

Time is; returns the system time. TimeValue is not.

 

[stumac]

Time is; returns the system time. TimeValue is not.

Timevalue is a valid function from access 2007 onwards.

https://support.office.com/en-us/article/timevalue-function-35d775cc-c6c7-49bb-8202-e338ac930e23

 

[Micron]

Thanks. I looked it up first and the M$ site information identified it as an excel function without referencing any other Office application.

 

[xenou]

Not sure that it matters but this code could probably be improved. Not sure - basically its running a loop for (hours?) until its time to create a backup, which seems like a huge amount of cpu being used to mostly do nothing.

But I'm not familiar with backups for running Access databases. I have always backed up databases in the middle of the night - but they would be databases not currently in use. I use a system scheduler (windows task) to kick that off. In simple terms, you can just copy the db file.

If you need a backup of a running db then you could probably do something like the above as well - wake up a scheduled task that creates an instance of the target db and runs the code.

 

[MikeBart]

Thanks all, I replaced

 dTime = InputBox("Create a backup at", , Time + TimeValve("12:00:01")) for  [code] dTime = TimeValue("12:11:05") [\code]; Works fine