Created a VBA Macro in Access 2007, but no clue how to make it run

[SP117A]

I don't know how to execute a VBA. It's like Access doesn't recognize the VBA I wrote. It only wants to recognize macros created by adding that out of the box functions. How do I write VBA code so it is available to add it to the RunMacro? I just want to have the VBA code execute when the Access database is opened. I ran the code and the code works just fine.

[pbaldy]

Let's clarify terms. There is VBA code and there are macros; the terms are not interchangeable, and there is no such thing as a "VBA macro" (Excel confuses this issue as they call VBA code a macro). If you want to have VBA code execute when the database is opened, you can either call it using RunCode in a macro named autoexec, or call it from the open event of whatever form opens with the database. For the former, the code would probably have to be a public function in a standard module.

[SP117A]

Thank you. The challenge is that I don't know how to attach the VBA code to a RunCode.

Trying to execute the following code probably by using Task Manager to pull up Access. Was trying to have it execute if Access was left open at a certain time, but it never worked. If I ran the VBA code, and it was the correct hour, then it would run properly. The challenge is getting the VBA code to execute.

Sub autoexec()Dim strNewFileName, strFileName, strFullTargetPath, strSourcePath As String
Dim strOldEntirePath, strNewEntirePath As String
Dim InResult As Long
Dim NewDBName As String, DBName As String
Dim BackupDB As Object
strFileName = "TestBackup-Originalv2.accdb"
strSourcePath = "X:\TestMe\SecureDatabase\"
strFullTargetPath = "X:\TestMe\SecureDatabase\backup\"
strOldEntirePath = strSourcePath + strFileName
If Hour(Now) = 10 Then
'If Minute(Now) = 50 Then
strNewFileName = Left(strFileName, InStr(1, strFileName, ".") - 1) & Format(Day(Now()), "yyyymmdd") & ".accdb"
strNewEntirePath = strFullTargetPath + strNewFileName
DBEngine.CompactDatabase strOldEntirePath, strNewEntirePath
'End If
End If
End Sub

[pbaldy]

Try a macro named autoexec, change the above to a function rather than a sub (I'd change the name for clarity), and call it from the macro with RunCode.

[SP117A]

Thank you! Works a little different then Excel, but now I am starting to understand how Access is set up. Macro runs the VBA code just fine.

[pbaldy]

Happy to help, and welcome to the site by the way! Excel definitely confuses things. "Record a macro" in Excel creates VBA code.