[computerman29642]

Does anyone know if it is possible to run a batch file when Access 2003 database is opened? If so, how would that be done?

[computerman29642]

Let me know if having an example of the batch file would help.

[Rollin_Again]

Open Access and press ALT + F11 to open the VB editor. Click INSERT >> MODULE and paste the code below into the blank code module. Just change the path of the batch file. Then create a new macro in Access by clicking on the macro link and then pressing the New button. The macro builder should now open and you will create the Access macro. At the top of the macro builder select RunCode as your action. At the bottom of the macro builder will be a text field for you to enter your function name. This is where you will put the name of the function you previously created called CallBatch (). When you click the SAVE button you will be prompted to name your macro. Just name it AutoExec and it will fire automatically when the database is opened.

Public Function CallBatch()
Shell "C:\MyBat.bat"
End Function

Regards,
Rollin

[Rollin_Again]

What exactly does the batch file do? You can probably just bypass the batch file and do everything using VB script.

Regards,
Rollin

[computerman29642]

The first thing the batch file does is makes a copy of the database for backup. Then the batch file compacts and repairs the database.

The batch file is failing on the compact and repair part. I get an error message stating the database is already opened.

[Rollin_Again]

Here is a link with sample code to copy and compact a database using VBA.

http://forums.devarticles.com/micros...vba-24958.html

Feel free to post your code from your batch file.

Regards,
Rollin

[computerman29642]

Here is the code I have in my batch file...

@echo off

REM Creates a copy of the MDB file, and places the file in the Backup Directory

XCOPY "C:\Movie\Movie.mdb" "C:\Movie\Backup" /D /y

REM Compacts and Repairs the DB

"C:\ProgramFiles\MicrosoftOffice\Office\msaccess.exe" "C:\Movie\Movie.mdb" /compact /repair

Mine seems a little bit simpler than the code found in the link you provided. Maybe that is why mine does not work....LOL!

[computerman29642]

I tried using portions of the code in the link provided, but I cannot seem to get the code to work.

Any thoughts?

[Rollin_Again]

Originally Posted by gamecockfan

I tried using portions of the code in the link provided, but I cannot seem to get the code to work.

Any thoughts?

Are you getting an error? What is happening?

Regards,
Rollin

[computerman29642]

I want the code to run when I open the database, so I placed the code behind a function. I named the Macro "AutoExec". Nothing happens. The database does not compact & repair, and no backup is created.

[Rollin_Again]

Replace all your VBA code with a simple message box to see if the code is even being fired by the AutoExec macro.

You do have the AutoExec macro set to call the function you created right? When you are building the macro in Access you should be able to click the three little dots next to the function name text field and drill through and pick the function.

NOTE: If you are manually typing the function name to fire in the macro builder you need to include the parentheses after the function name.

Regards,
Rollin

[computerman29642]

The messge box worked. I guess I screwed up the code some how.

[Rollin_Again]

Post your code please.

Regards,
Rollin

[computerman29642]

Here is the modified code...

Option Compare Database
Option Explicit

Public Function Test()
Dim dbPath As String, OldDbName As String, NewDbName As String, DbBackup As String
Dim Response As Integer, fs As Object
dbPath = Application.CurrentProject.Path
OldDbName = "ReorderMonitoring" & ".mdb"
NewDbName = "ReorderMonitoringNew" & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "mmddyy") & ".mdb"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
Set fs = Nothing
MsgBox "Did this work"
End Function

I get some kind of popup box letting me know the action failed. The only option I have is to press the Halt command button on the popup box.

[computerman29642]

I believe it is having an issue with this line.

fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup