Solved

Access

Posted on 2014-03-03
9
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

710 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