Solved

Scheduler needed

Posted on 2002-05-09
20
283 Views
Last Modified: 2010-05-02
Where can I get a scheduler to run procedures in Acces at certain times. e.g I want to do time calculations for employees at 1AM in the morning.

Thanks

deneuve
0
Comment
Question by:deneuve
  • 7
  • 5
  • 3
  • +4
20 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6998572
use the Windows Scheduler to start your EXE. You can find it into the Control Panel under "Scheduled Tasks".
0
 
LVL 15

Expert Comment

by:robbert
ID: 6998682
In case your operation system doesn't have the Windows Scheduler (like Win9x, NT4), you can also use the DOS AT command (for help type: "at /?") or a VB scheduler:
http://www.vbadmincode.btinternet.co.uk/winnt.htm#Scheduling

To run Access procedures from an VB EXE, you would code something like:

    Dim objAccess   As Access.Application
   
    objAccess.OpenAccessProject "path"
    objAccess.Run "procedure", "argument"
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6998945
I wrote a simple VB scheduling application that allows you to schedule the running of an EXE at either daily (you pick the time), weekly (you pick the day of the week) or monthly (you pick the day of the month) intervals.  You specify the path to the EXE and some other basic information.

Then, in your case, you'd need some code as robbert is suggesting that could be a VB EXE that will instantiate an Access Application object and run whatever queries, or macros you have there (the code for this would be pretty simple to prototype for you.

If you'd like the vb scheduling code, post an e-mail address and I'll send it along.  I haven't opened the project for a long while, so, I don't know if it uses any special OCXs (I'm pretty sure that it was a VB 3 application).  If it does, then you might have a little work ahead to get it working again, but it's a good place to start.
0
 

Author Comment

by:deneuve
ID: 6998972
Thanks mdougan

my email is deneuve@eircom.net

I do not intend to open Access to update the tables
Could I build in the scheduler into my app so that the user can schedule the tasks from within the app.I want to call procedures from within the app to update the tables and so have everything as one unit.

Or perhaps I should have several exes corresponding to the different night jobs that has to be run then if I need to modify one of these jobs I need only recompile the small exe

what do you think experts

Kathy
0
 

Author Comment

by:deneuve
ID: 6998975
The reason I am not opening Access is that users may have different versions of Access so there would be a problem

Kathy
0
 

Author Comment

by:deneuve
ID: 6998977
I have just re read my question. I am sorry for misleading you all.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6999226
I think your question was clear.  

"Or perhaps I should have several exes corresponding to the different night jobs that has to be run then
if I need to modify one of these jobs I need only recompile the small exe"

This is the way that I've done it in the past.  Let the scheduler concentrate on scheduling, let the little EXEs concentrate on doing whatever they need to do.

Can you give an example of one of the types of updates you want to do?  If you want to update rows in a table, then you're correct, you can do that with just ADO queries.  If you wanted to run a macro that you've already created in your access database, then you'd do that with OLE Automation and Access objects.

I'll send off the scheduler tonight when I get home.
0
 

Author Comment

by:deneuve
ID: 6999285
Thanks again Mike.

I have not written the the updates yet but they will be just ADO queries and a lot of them. I want to stay away from using Access objects so that I do nothave to handle Version problems.

Look forward to the scheduler

Kathy
0
 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
ID: 6999446
As far as the ADO Updates, I don't know if you are in need of the syntax for that, but my favorite method is to use command objects with parameters.  Parameters take care of all of the problems encountered with strange SQL unfriendly characters like single or double quotes.  They take a little more code, but I find it's worth it:

Dim CN As ADODB.Connection
Dim CMD As ADODB.Command

Private Sub Command1_Click()
' assumes the connection CN has already been opened
Dim sMsg As String
Dim i As Long
Dim Parm1 As ADODB.Parameter
Dim Parm2 As ADODB.Parameter

On Error GoTo ErrorRtn

' instantiate a new parameter object
Set Parm1 = New ADODB.Parameter
Parm1.Direction = adParamInput
Parm1.Type = adInteger
Parm1.Value = txtAge.Text

' instantiate a new parameter object
Set Parm2 = New ADODB.Parameter
Parm2.Direction = adParamInput
Parm2.Type = adVarChar
Parm2.Size = 50
' So, this textbox could have the name O'Brian without causing a problem
Parm2.Value = txtLastName.Text

' instantiate a new command object
Set CMD = New ADODB.Command
' set the connection
CMD.ActiveConnection = CN
CMD.CommandType = adCmdText
' put question-marks in as the place holders for the params
CMD.CommandText = "Update Employees Set Age = ? where LastName = ?"

' Important that you append them in the order that they are
' to appear in the query
CMD.Parameters.Append Parm1
CMD.Parameters.Append Parm2

' Execute, and get an optional record count if you want
CMD.Execute lRecCount

MsgBox lRecCount & " Records affected"

ExitRtn:
   
    Exit Sub

ErrorRtn:
' A single query might return multiple errors    
    If CN.Errors.Count > 0 Then
        For i = 0 To CN.Errors.Count - 1
' Sometimes the NativeError number is more helpfull
            sMsg = sMsg & CN.Errors(i).Number & " - " & CN.Errors(i).Description & " - " & CN.Errors(i).NativeError & vbCrLf
        Next i
        MsgBox sMsg
    Else
        MsgBox Err.Description
    End If
    GoTo ExitRtn
End Sub
0
 

Author Comment

by:deneuve
ID: 7000713
Thanks Mike for the scheduler and the Parameter example. This will be helpful as there are a lot of O's in this part of the world O'Brien, O'Donnell, O'Meara....and on and on and on.

Kathy
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:deneuve
ID: 7001452
You have done a great job on the scheduler. It works fine and the code is very clear. Hope to be in touch again

Thank You

Kathy
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7001545
Anytime, thanks Kathy!
0
 
LVL 15

Expert Comment

by:robbert
ID: 7002150
scheduler needed?
0
 

Author Comment

by:deneuve
ID: 7002453
Nice one robbert!!

Kathy
0
 

Expert Comment

by:kltan
ID: 7644167
Hi mdougan,

appreciate if you can provide me the code for your scheduler. I'm working on an app to read from an access table as a reminder for many, many, events per day, week, month, etc. email is kltan@rocketmail.com

Thanks, Kee Leong.

Ref:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20298665.html
0
 

Expert Comment

by:jondavid55408
ID: 8166687
hey mdougan,

could you please sent me your scheduler code? i'm building an app to grab records from excel, update access tables, write to a log, then send out some emails based on the updates, and i'm trying to figure out how to do schedule based work, like executing DTS packages, but using only VB in Access. as in the above, i only need to execute ADO updates and inserts based on the data i import, then figure out the mail piece. but i'd love to get a peek at your scheduler to help me along.

thanks!

jonDavid

EE@jondavid.com

0
 
LVL 18

Expert Comment

by:mdougan
ID: 8166848
Sure, as long as you promise not to turn around and sell your code (including my code) as a scheduling program that competes with mine. (doesn't sound like it would).  You can download the runtime version from my website at:

www.greatsoftware.net

I'll send the code from home later on.

Mike
0
 

Expert Comment

by:jondavid55408
ID: 8167042
hey mdougan,

could you please sent me your scheduler code? i'm building an app to grab records from excel, update access tables, write to a log, then send out some emails based on the updates, and i'm trying to figure out how to do schedule based work, like executing DTS packages, but using only VB in Access. as in the above, i only need to execute ADO updates and inserts based on the data i import, then figure out the mail piece. but i'd love to get a peek at your scheduler to help me along.

thanks!

jonDavid

EE@jondavid.com

0
 

Expert Comment

by:jondavid55408
ID: 8167075
thanks! I appreciate it very much! so you know, I have no intention of making your code a product or selling it or anything like that - just want to build a tool to enhance my non-commercial, very lite CRM application. i'll probably hack it up beyond recognition, as that's generally how i learn.

thanks again,

jonDavid
0
 

Expert Comment

by:JoshuaAMS
ID: 12189825
Hi mdougan

Would you mind sending you scheduling code to one more person? Not a very experienced VB programmer and need to find a way to schedule record alterations in a database. email is mac@iol.ie.

Thanks
JoshuaAMS
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now