06-11-2019, 07:02 PM
|
#1
|
Newly Registered User
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
|
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
|
|
|
06-11-2019, 07:20 PM
|
#2
|
AWF VIP
Join Date: Mar 2014
Location: The Great Land
Posts: 2,451
Thanks: 0
Thanked 571 Times in 567 Posts
|
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.
|
|
|
06-11-2019, 09:04 PM
|
#3
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
|
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.
|
|
|
06-12-2019, 04:54 AM
|
#4
|
Newly Registered User
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
|
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
|
|
|
06-12-2019, 07:24 AM
|
#5
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
|
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.
|
|
|
06-13-2019, 01:03 PM
|
#6
|
Newly Registered User
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
|
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!
|
|
|
06-13-2019, 11:49 PM
|
#7
|
Enthusiastic Amateur
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,508
Thanks: 439
Thanked 838 Times in 809 Posts
|
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
Please, please use code tag # when posting code snippets
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
06-14-2019, 07:06 AM
|
#8
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
|
Re: automated email
Quote:
Originally Posted by Gasman
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.
|
|
|
06-14-2019, 07:09 AM
|
#9
|
Enthusiastic Amateur
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,508
Thanks: 439
Thanked 838 Times in 809 Posts
|
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
Please, please use code tag # when posting code snippets
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
06-14-2019, 07:12 AM
|
#10
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
|
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.
|
|
|
06-14-2019, 11:42 AM
|
#11
|
Newly Registered User
Join Date: Mar 2002
Posts: 357
Thanks: 34
Thanked 3 Times in 3 Posts
|
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
|
|
|
06-14-2019, 12:40 PM
|
#12
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
|
Re: automated email
Quote:
Originally Posted by supmktg
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.
|
|
|
06-14-2019, 12:26 PM
|
#13
|
AWF VIP
Join Date: Mar 2014
Location: The Great Land
Posts: 2,451
Thanks: 0
Thanked 571 Times in 567 Posts
|
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.
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Hybrid Mode
|
|
All times are GMT -8. The time now is 02:54 PM.
|
|