Solved

Access

Posted on 2014-03-03
9
234 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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