Scheduler needed

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
deneuveAsked:
Who is Participating?
 
mdouganCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
use the Windows Scheduler to start your EXE. You can find it into the Control Panel under "Scheduled Tasks".
0
 
robbertCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mdouganCommented:
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
 
deneuveAuthor Commented:
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
 
deneuveAuthor Commented:
The reason I am not opening Access is that users may have different versions of Access so there would be a problem

Kathy
0
 
deneuveAuthor Commented:
I have just re read my question. I am sorry for misleading you all.
0
 
mdouganCommented:
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
 
deneuveAuthor Commented:
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
 
deneuveAuthor Commented:
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
 
deneuveAuthor Commented:
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
 
mdouganCommented:
Anytime, thanks Kathy!
0
 
robbertCommented:
scheduler needed?
0
 
deneuveAuthor Commented:
Nice one robbert!!

Kathy
0
 
kltanCommented:
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
 
jondavid55408Commented:
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
 
mdouganCommented:
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
 
jondavid55408Commented:
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
 
jondavid55408Commented:
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
 
JoshuaAMSCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.