Automate Process

[AccUser]

(Okay, let's see who really good at Access/VB.)

I have a macro shortcut on my desktop. When executed, it will open the database, run a query that prompts for a starting date (YYYYMM) and a ending date (YYYYMM). The starting date is always current month/year and ending date is always 12 month later (ex. 200001 - 2000112).

Next, the query will export a text file with the related information from the tables.

When completed, the database will close.

Is there any way to automate this process so that the macro will run with no user intervention at all, say every Thursday night? Also, would like this file sent to a particular user as an attachment within the network.

Thanks in advance for your assistance in this.

 

[JerryDennison]

The easiest way to accomplish this is with a scheduler program. For something similar(I run a dos script and Access db every morning at 8:00am) I use Event Manager. It is a shareware product that has not failed me in over 4 years of daily use.

http://www.srosystems.com

 

[AccUser]

I will download and try program. But, how do I get the information that is required for the parameters? (Start/Ending)

 

[RickSpr]

I'll make the following assumptions:
1. "2000112" was supposed to be "200012", which is actually 11 months later, so that your range encompasses 12 months.
2. Your query is an Append query whose output table is a linked text file, so you don't have a separate macro step that does the export.
3. You are using query parameters to get the prompt, and the query parameters are of type Text. If they're Long Integers, some minor changes are needed.

Create a standard module and copy the following code into it:

Public Function ExtractYearData()
    Dim qdf As QueryDef
    
    Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")
    ' change "StartDate" and "EndDate" to the names of your query parameters
    qdf.Parameters("StartDate") = YyyyMm(Date)
    qdf.Parameters("EndDate") = YyyyMm(DateAdd("m", 11, Date))
    qdf.Execute
    Set qdf = Nothing
    ' This is where you would send the file to the user.
End Function

Public Function YyyyMm(dt As Date) As String
    YyyyMm = Format$(DatePart("yyyy", dt), "0000") _
        & Format$(DatePart("m", dt), "00")
End Function

If you're using Access 2000, then before closing the Visual Basic window, add a reference to the Microsoft DAO 3.6 Object Library. You need that for the QueryDef object.

Next, change the macro action that currently runs your query to RunCode, and set the function name to ExtractYearData().

It wasn't clear how you "sent the file as an attachment". Do you mean an email attachment? I haven't actually coded any Mail API stuff, so I'd best leave that part to somebody else.

To schedule your macro to run without intervention, on Windows 98/Me you can just copy it into the Scheduled Tasks folder and set up the schedule time.