Newbie wants to batch process queries in Access

[jimbo2112]

Hi All,

I am a database newbie and have been working on a database to supply a catalogue to our sales team. I have developed a query that strips away what they do not need to see and I have developed an Excel macro that fully formats the query to make it pretty!

What I now want to do is:

* Write a .bat file to automate a standard group of MS Access queries
* Export the queries to Excel
* Automate the formatting of each query in Excel
* Move the formatted files to an FTP folder for others to download
* Schedule this automation task to happen weekly

So ... can this be done fairly easily? Has anyone done anything similar?

Many thanks for your anticipated help on this one!

Cheers

Jimbo

 

[jimbo2112]

OK ... I have been looking at this since I wrote the request.

I now think that I can manage the running of the macro to generate the reports in MS Access, and export the resulting Excel files to any location I specify. But I will still need to invoke this action remotely. which is where the .bat file would come in?

I hope this is the correct forum area to be asking this in? Please feel free to relocate the thread if you think I could get a better suited audience!

Jimbo

 

[petehilljnr]

Make a start-up form for your database.
Get your macros to run on the On Open event of the form.
When the macros are finished, create another macro to close the database.
Use the Scheduler on your computer to open the database automatically at the times your specify.

So now - the Scheduler will open the db, the start up form will open, the queries will be run and exported, then the db will close. Done!

Regards,
Pete.

 

[The_Doc_Man]

Another approach:

Look in Access Help for Command Line options and focus on option -X

You can use that to open a database, run a macro, and the macro can do what you want it to. Just remember to make the macro end with a QUIT action so that the DB instance will close.

If you make an icon that opens Access to that database, you can include the name of the correct workgroup and the name of the macro by opening the properties of the icon (right-click) to edit the command line.

Now to make this run on a machine left on overnight, put the icon in your Windows Task Scheduler to run as a user - such as yourself. That's as close to a SCHEDULED batch job as possible.

If you have the "special" icon where folks can just run it - as themselves - you need to assure that everyone who CAN run it has permissions to what they need to run it successfully.

 

[jimbo2112]

Hi Chaps,

Thanks for your wise replies! I will have a go and let you know how I get on!

Cheers

Jimbo

 

[Bob Miller]

OK ... I have been looking at this since I wrote the request.

I now think that I can manage the running of the macro to generate the reports in MS Access, and export the resulting Excel files to any location I specify. But I will still need to invoke this action remotely. which is where the .bat file would come in?

I hope this is the correct forum area to be asking this in? Please feel free to relocate the thread if you think I could get a better suited audience!

Jimbo

If this is a catalogue, why would you send it out in crappy Excel format. Why not just place your reports from Access on the ftp. You have a number of options for output format including .pdf if you have that ability.

 

[jimbo2112]

If this is a catalogue, why would you send it out in crappy Excel format. Why not just place your reports from Access on the ftp. You have a number of options for output format including .pdf if you have that ability.

Bob,

I guess it is down to what I know. The report function would have been another thing to learn and my schedule is tight, whereas I already have a fair grip of Excel and it makes the end user format manipulable if they want to hack it about with the customer.

Cheers

Jimbo

 

[chrisdc]

Catalogue

If this is a catalogue, why would you send it out in crappy Excel format. Why not just place your reports from Access on the ftp. You have a number of options for output format including .pdf if you have that ability.

I am trying to build a catalogue using a report, but the problem is i can seem to get 4 column of different pictures - at the moment i'm getting 4 columns of the same picture.

Any ideas???

Chris

 

[Bob Miller]

I am trying to build a catalogue using a report, but the problem is i can seem to get 4 column of different pictures - at the moment i'm getting 4 columns of the same picture.

Any ideas???

Chris

I assume that you have placed 4 picture objects on your page which gives you four of the same picture. You would need to set you page to 4 columns and place only one picture object Go to Page setup under File with the report in design view. Select the columns tab and change the number to 4. You will then need to make your report width the size of one column. Hope this helps. PM me if you need additional assistance.

 

[chrisdc]

Thanks Bob - I have spent a number of hours finding this information.

It is much appriaciated.

thanks again

chris