Access

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.
mkramer777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
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
0
omgangIT ManagerCommented:
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
0
omgangIT ManagerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mkramer777Author 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?
0
omgangIT ManagerCommented:
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
0
mkramer777Author Commented:
I'm a bit of a novice on this.  Where do I find and copy the code from access?
0
omgangIT ManagerCommented:
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
0
Jim P.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

0
omgangIT ManagerCommented:
I like it.
OM Gang
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.