Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 07:02 PM   #1
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
automated email

I have a Access app that uses an Azure SQL Server database on Sharepoint. I need to send an email at the end of each workday that informs mgmt of orders rec'd but not yet processed by the end of the day.

I began designing the function in Access, but if no user is logged in at the designated time that is end of day the email will not get sent. I believe that this functionality is available in SQL Server, but I am not sure where to begin.

Can someone point me in the right direction?

Thanks,
Sup

supmktg is offline   Reply With Quote
Old 06-11-2019, 07:20 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,960
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: automated email

Read a suggestion elsewhere to automate SQLServer: "write a manual ETL script to move the data, then put that script in a SQL Agent job and schedule it to run as needed". I have no idea what ETL and Agent job are nor how to schedule. But maybe you could do some research on those items.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-11-2019 at 09:47 PM.
June7 is online now   Reply With Quote
Old 06-11-2019, 09:04 PM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,876
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: automated email

I haven't used ETL, but I have a number of automated emails sent out from SQL Server. Your requirement is a little vague, but you'll probably end up with a stored procedure run from SQL Server Agent, using Database Mail to send the email. If you need to loop records to put data in the body, a cursor can be used.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-12-2019, 04:54 AM   #4
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: automated email

My knowledge of SQL Server is pretty much limited to the database tables, views and stored procedures. In this case, I am connecting an Access front end and some asp.net web forms to a client's Azure database that is administered by an outside Sharepoint consultant. I am given limited SQL Server permissions, and it is likely that my permissions do not include SQL Server Agent. I do not see SQL Server Agent in Management Studio (v17.9.1, NOT Express),

Before I get into a tug of war with the Sharepoint consultant, are there other possibilities to send an automated email on a daily basis that do not include SQL Server Agent?

Thanks,
Sup
supmktg is offline   Reply With Quote
Old 06-12-2019, 07:24 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,876
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: automated email

I don't know if it's a permissions thing or an Azure thing, as I haven't used Azure. Can the PC be locked instead of logged out of? I can't recall if the Access db will run at all if the PC is not logged in. If it will, you could use CDO to send the email, which I don't think will require a user to be logged in like Outlook automation would. I guess I'm lucky because I've always been able to lock a PC rather than log out of it and all my automated stuff runs fine.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-13-2019, 01:03 PM   #6
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: automated email

I've discovered that Azure SQL no longer includes SQL Agent. For others that happen upon this post, my research found 2 alternative methods to send a scheduled email from Azure:
https://vpolizzi.wordpress.com/2010/...-on-sql-azure/
https://docs.microsoft.com/en-in/azu...-to-send-email

I may try this at a later date, but for now I will most likely have my Access app send the email on the first user shutdown after 4:30pm, and log it into a sent mail table so no further emails will be sent for that day, or send it upon the first login in the morning of the next day. It is not optimal, but it is my best choice for now.

Thanks to both of you!
Sup

If someone has a better idea, I'm all ears!
supmktg is offline   Reply With Quote
Old 06-13-2019, 11:49 PM   #7
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,673
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: automated email

Could you not use Task Scheduler and open the DB to run that particular code.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 06-14-2019, 07:06 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,876
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: automated email

Quote:
Originally Posted by Gasman View Post
Could you not use Task Scheduler and open the DB to run that particular code.?
I think the key bit was "if no user is logged in at the designated time that is end of day the email will not get sent.".
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-14-2019, 07:09 AM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,673
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: automated email

Sorry, I took that to be logged in to the DB, not the network.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 06-14-2019, 07:12 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,876
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: automated email

I could be the one who misunderstood.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-14-2019, 11:42 AM   #11
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: automated email

For clarity's sake, I meant logged in to the db.

With all of your guidance and some additional research I have decided to build a separate app that includes the required linked sql tables and module that contains my send email via outlook vba function. From there I will use a windows scheduled task to open the db, run the vba code and close the db.

Hopefully, the client will follow my instructions and leave the windows machine on 24/7.

Again, Thanks for all of your guidance,
Sup
supmktg is offline   Reply With Quote
Old 06-14-2019, 12:26 PM   #12
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,960
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: automated email

Guess I misunderstood original post and caused a bit of misdirection. I have a VBScript that runs VBA procedure in db. Windows TaskScheduler runs the script. But just opening db and its code closes itself should work just as well.

I no longer work in that office and if I remember correct, had to keep computer logged into NW for TaskScheduler to work. But maybe someone will confirm that.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-14-2019, 12:40 PM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,876
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: automated email

Quote:
Originally Posted by supmktg View Post
For clarity's sake, I meant logged in to the db.

With all of your guidance and some additional research I have decided to build a separate app that includes the required linked sql tables and module that contains my send email via outlook vba function. From there I will use a windows scheduled task to open the db, run the vba code and close the db.
I misunderstood too. That's why I asked if the computer could remain locked instead of logged off. I have a bunch of automated imports/exports/emails running as you describe.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
email automation

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VB Code to add c.c Email addresses into automated email. southwestgooner Modules & VBA 8 08-25-2015 03:56 AM
Help with automated email outofpractice Modules & VBA 1 06-07-2013 11:50 AM
Automated Email nac1987 Modules & VBA 1 09-15-2010 05:12 AM
Outlook AUtomated email with the "Program sending email on your behlaf " warning paulmcdonnell Modules & VBA 5 07-27-2005 10:11 AM
Automated email philbertson Modules & VBA 4 02-03-2005 09:36 AM




All times are GMT -8. The time now is 01:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World