Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access

Posted on 2014-03-03
9
Medium Priority
?
241 Views
Last Modified: 2014-03-11
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.
0
Comment
Question by:mkramer777
  • 6
  • 2
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39900704
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
 
LVL 28

Expert Comment

by:omgang
ID: 39900732
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
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 39900739
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mkramer777
ID: 39900748
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
 
LVL 28

Expert Comment

by:omgang
ID: 39900834
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
 

Author Comment

by:mkramer777
ID: 39900842
I'm a bit of a novice on this.  Where do I find and copy the code from access?
0
 
LVL 28

Expert Comment

by:omgang
ID: 39900878
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39903468
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
 
LVL 28

Expert Comment

by:omgang
ID: 39903518
I like it.
OM Gang
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question