Access

mkramer777
mkramer777 used Ask the Experts™
on
I have an access database that loads a text file into the database.  I have to hit a button on the database form to do this.  Is there a way to automate this?  I want it to run nightly at a certain time.  Maybe I need some other software to do this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
omgangIT Manager

Commented:
You don't need additional software to do this.  You can create a Macro to call the function that performs the import.  Then you can create a Windows Scheduled Task to launch Access as the prescribed time and execute the Macro.  Let me dig up the specific command line for the Task Scheduler.
OM Gang
omgangIT Manager

Commented:
In the Windows Task Scheduler you need to specify as the Run command

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "c:\Temp\AccessDBName.mdb" /x "MacroName"

Let me know if you need help setting this up.
OM Gang
IT Manager
Commented:
Sorry, forgot to describe the command

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "c:\Temp\db1.mdb" /x "MacroName"

The first part is the path to the MSAccess executable - should be similar to what I pasted.
The second part is the path to your Access database.
The third part is an X switch to run the specified Macro followed by the Macro name in quotes.

OM Gang
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
What if this is a win xp pro machine running access 2000?  Also, can you send any documentation on writing the macro or is that pretty simple?
omgangIT Manager

Commented:
Works for Win XP Pro.
To create the Macro is pretty straight forward.

Please post the code for your current button_click routine.  We'll start there, modify it to make a stand-alone function and then create a Macro to call that function.
OM Gang

Author

Commented:
I'm a bit of a novice on this.  Where do I find and copy the code from access?
omgangIT Manager

Commented:
On your form, in Design view, select the button.  In the Properties dialogue, for the OnClick event you should see [Event Procedure].  Double-click that to open the VBE (Visual Basic Editor) and display the buttons OnClick procedure.  Copy the entire procedure and paste it here.
OM Gang
Most Valuable Expert 2014

Commented:
Actually the way I do it is I have form that opens with the database opening.  The On Load event is as shown below. You don't even see the form blink by if you open it outside the time in the code. Then the scheduled task is just a simple open the DB command. And if you want to open the DB to do edits it opens normally and brings up the DB window.

And if it is a user interface database, you could open the user form. Or put it in that form's on load event.
Private Sub Form_Load()

DoCmd.RunCommand acCmdSizeToFitForm

'Me.Run_Import.SetFocus

If TimeValue(Now()) > #7:00:00 AM# And TimeValue(Now()) < #8:00:00 AM# Then
    RunImports
    DoCmd.Quit acQuitSaveAll
End If

DoneAndQuit

End Sub


Private Function DoneAndQuit()

DoCmd.Close acForm, "AutoRun", acSaveYes

End Function

Open in new window

omgangIT Manager

Commented:
I like it.
OM Gang

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial