Using Access to auto-email reports

Yahoo Archive
1
Brian,

Worked perfect, thanks!

Todd



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of B_Stinger
Sent: Tuesday, November 16, 2010 8:28 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Using Access to auto-email reports





Todd,

We're on Vantage 6.1 also, using Access XP and I auto e-mail a bunch of
reports with Access.

When I link a Vantage table to Access via ODBC the dialog that shows the
table list includes a check box labeled "Save Password". If I click
that, the ODBC password is saved and users don't need to know what it is
to activate the ODBC connection. Hopefully Access 2010 has something
similar.

As far as the Outlook warning goes, I got a free utility to handle that.
It's called Click Yes, it will respond to those outlook security
warnings automatically.

HTH,

Brian Stenglein

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Todd Gilbert" <tgilbert@...> wrote:
>
> I've got some reports in Access (2010) that I want to run in the
middle
> of the night and send out as attachments. I've gotten the Autoexec
> Macro combined with the Windows Scheduler to work to do this...so long
> as I'm sitting here to enter the ODBC connection password and hit
> 'Allow' when Outlook warns me that a program is trying to send an
email
> on my behalf.
>
>
>
> Any ideas how I can automate the ODBC connection and "allow" the
emails
> to go through?
>
>
>
>
>
> Thanks to all,
>
>
>
> Todd Gilbert
>
> GCM, Inc.
>
> Vantage 6.1
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
2
I've got some reports in Access (2010) that I want to run in the middle
of the night and send out as attachments. I've gotten the Autoexec
Macro combined with the Windows Scheduler to work to do this...so long
as I'm sitting here to enter the ODBC connection password and hit
'Allow' when Outlook warns me that a program is trying to send an email
on my behalf.



Any ideas how I can automate the ODBC connection and "allow" the emails
to go through?





Thanks to all,



Todd Gilbert

GCM, Inc.

Vantage 6.1



[Non-text portions of this message have been removed]
3
Could you add the password to the registry? Just a thought, not sure if
it would work.



Thanks,

Blake Clemens

IT Systems Engineer

Delmarva Millwork Corporation

(800) 360-2364 x132

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Gilbert
Sent: Monday, November 15, 2010 4:27 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Using Access to auto-email reports





I've got some reports in Access (2010) that I want to run in the middle
of the night and send out as attachments. I've gotten the Autoexec
Macro combined with the Windows Scheduler to work to do this...so long
as I'm sitting here to enter the ODBC connection password and hit
'Allow' when Outlook warns me that a program is trying to send an email
on my behalf.

Any ideas how I can automate the ODBC connection and "allow" the emails
to go through?

Thanks to all,

Todd Gilbert

GCM, Inc.

Vantage 6.1

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
Todd,

Not sure in 2010 version but I think this worked in Access 2003.



Try looking under "Options" , "Edit/Find" and uncheck the box for
"Action Queries" in the confirm section.



Chris



_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Gilbert
Sent: Monday, November 15, 2010 1:27 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Using Access to auto-email reports





I've got some reports in Access (2010) that I want to run in the middle
of the night and send out as attachments. I've gotten the Autoexec
Macro combined with the Windows Scheduler to work to do this...so long
as I'm sitting here to enter the ODBC connection password and hit
'Allow' when Outlook warns me that a program is trying to send an email
on my behalf.

Any ideas how I can automate the ODBC connection and "allow" the emails
to go through?

Thanks to all,

Todd Gilbert

GCM, Inc.

Vantage 6.1

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
5
Chris, Unfortunately this doesn't seem to work in 2010.

As far as the ODBC goes, is there a way to embed the connection string in the SQL so it doesn't prompt for a password?

Todd
--- In vantage@yahoogroups.com, "Chris Crosta" <chris@...> wrote:
>
> Todd,
>
> Not sure in 2010 version but I think this worked in Access 2003.
>
>
>
> Try looking under "Options" , "Edit/Find" and uncheck the box for
> "Action Queries" in the confirm section.
>
>
>
> Chris
>
>
>
> _____
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of Todd Gilbert
> Sent: Monday, November 15, 2010 1:27 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Using Access to auto-email reports
>
>
>
>
>
> I've got some reports in Access (2010) that I want to run in the middle
> of the night and send out as attachments. I've gotten the Autoexec
> Macro combined with the Windows Scheduler to work to do this...so long
> as I'm sitting here to enter the ODBC connection password and hit
> 'Allow' when Outlook warns me that a program is trying to send an email
> on my behalf.
>
> Any ideas how I can automate the ODBC connection and "allow" the emails
> to go through?
>
> Thanks to all,
>
> Todd Gilbert
>
> GCM, Inc.
>
> Vantage 6.1
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
6
Its the COM security that's tripping you up. There are programs that can disable the popup, someone asked something very similar, but I cannot remember for the life of me what its called.

Alternatively, if you can get access to dump the report to HTML into a directory. You can write a simple Dot.net program what can create and send a HTML email using your internal mail server (takes about 20 ~lines of code).

--- In vantage@yahoogroups.com, "Todd Gilbert" <tgilbert@...> wrote:
>
> I've got some reports in Access (2010) that I want to run in the middle
> of the night and send out as attachments. I've gotten the Autoexec
> Macro combined with the Windows Scheduler to work to do this...so long
> as I'm sitting here to enter the ODBC connection password and hit
> 'Allow' when Outlook warns me that a program is trying to send an email
> on my behalf.
>
>
>
> Any ideas how I can automate the ODBC connection and "allow" the emails
> to go through?
>
>
>
>
>
> Thanks to all,
>
>
>
> Todd Gilbert
>
> GCM, Inc.
>
> Vantage 6.1
>
>
>
> [Non-text portions of this message have been removed]
>
7
Todd, you should be able to add the password to the connection string for ODBC. It should look something like

" DSN=Vantage;uid=sysprogress;host=???;port=???;pwd=sysprogress "

This is how I've configured ours, and I've used this same connection string in Excel, SSRS, and .NET applications.

In order to use something like this, however, you'll have to make sure that you've added a system connection for ODBC under Control Panel - Administrative Tools - Data Sources(ODBC) - System DSN.

--- In vantage@yahoogroups.com, "mahsangilbert" <tgilbert@...> wrote:
>
> Chris, Unfortunately this doesn't seem to work in 2010.
>
> As far as the ODBC goes, is there a way to embed the connection string in the SQL so it doesn't prompt for a password?
>
> Todd
> --- In vantage@yahoogroups.com, "Chris Crosta" <chris@> wrote:
> >
> > Todd,
> >
> > Not sure in 2010 version but I think this worked in Access 2003.
> >
> >
> >
> > Try looking under "Options" , "Edit/Find" and uncheck the box for
> > "Action Queries" in the confirm section.
> >
> >
> >
> > Chris
> >
> >
> >
> > _____
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> > Of Todd Gilbert
> > Sent: Monday, November 15, 2010 1:27 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Using Access to auto-email reports
> >
> >
> >
> >
> >
> > I've got some reports in Access (2010) that I want to run in the middle
> > of the night and send out as attachments. I've gotten the Autoexec
> > Macro combined with the Windows Scheduler to work to do this...so long
> > as I'm sitting here to enter the ODBC connection password and hit
> > 'Allow' when Outlook warns me that a program is trying to send an email
> > on my behalf.
> >
> > Any ideas how I can automate the ODBC connection and "allow" the emails
> > to go through?
> >
> > Thanks to all,
> >
> > Todd Gilbert
> >
> > GCM, Inc.
> >
> > Vantage 6.1
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
8
Todd,

We're on Vantage 6.1 also, using Access XP and I auto e-mail a bunch of reports with Access.

When I link a Vantage table to Access via ODBC the dialog that shows the table list includes a check box labeled "Save Password". If I click that, the ODBC password is saved and users don't need to know what it is to activate the ODBC connection. Hopefully Access 2010 has something similar.

As far as the Outlook warning goes, I got a free utility to handle that. It's called Click Yes, it will respond to those outlook security warnings automatically.

HTH,

Brian Stenglein

--- In vantage@yahoogroups.com, "Todd Gilbert" <tgilbert@...> wrote:
>
> I've got some reports in Access (2010) that I want to run in the middle
> of the night and send out as attachments. I've gotten the Autoexec
> Macro combined with the Windows Scheduler to work to do this...so long
> as I'm sitting here to enter the ODBC connection password and hit
> 'Allow' when Outlook warns me that a program is trying to send an email
> on my behalf.
>
>
>
> Any ideas how I can automate the ODBC connection and "allow" the emails
> to go through?
>
>
>
>
>
> Thanks to all,
>
>
>
> Todd Gilbert
>
> GCM, Inc.
>
> Vantage 6.1
>
>
>
> [Non-text portions of this message have been removed]
>
9
FYI, I found a relatively inexpensive solution to the Outlook problem
with ClickYes Software: http://www.contextmagic.com/express-clickyes/



Now I just need to figure out how to embed my ODBC Connection String
within my Autoexec Macro in Access. [Any suggestions appreciated!]



Thanks,

Todd Gilbert



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of markbetts66
Sent: Tuesday, November 16, 2010 12:30 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Using Access to auto-email reports





Its the COM security that's tripping you up. There are programs that can
disable the popup, someone asked something very similar, but I cannot
remember for the life of me what its called.

Alternatively, if you can get access to dump the report to HTML into a
directory. You can write a simple Dot.net program what can create and
send a HTML email using your internal mail server (takes about 20 ~lines
of code).

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Todd Gilbert" <tgilbert@...> wrote:
>
> I've got some reports in Access (2010) that I want to run in the
middle
> of the night and send out as attachments. I've gotten the Autoexec
> Macro combined with the Windows Scheduler to work to do this...so long
> as I'm sitting here to enter the ODBC connection password and hit
> 'Allow' when Outlook warns me that a program is trying to send an
email
> on my behalf.
>
>
>
> Any ideas how I can automate the ODBC connection and "allow" the
emails
> to go through?
>
>
>
>
>
> Thanks to all,
>
>
>
> Todd Gilbert
>
> GCM, Inc.
>
> Vantage 6.1
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
10
Hi Todd,

I use a File DSN for all my connections in Crystal Reports. I'm not familiar with Access but I reckon it should work there as well.

The contents of the connection file are:

[ODBC]
DRIVER=Progress OpenEdge 10.1B driver
UID=SYSPROGRESS
DB=mfgsys803
PORT=8350
HOST=<server name>
DIL=READ UNCOMMITTED
PWD=sysprogress

I have a load of scheduled and manually run reports all built using this connection and so no-one has to enter a password when running them.

HTH,
Nigel.


--- In vantage@yahoogroups.com, "Todd Gilbert" <tgilbert@...> wrote:
>
> FYI, I found a relatively inexpensive solution to the Outlook problem
> with ClickYes Software: http://www.contextmagic.com/express-clickyes/
>
>
>
> Now I just need to figure out how to embed my ODBC Connection String
> within my Autoexec Macro in Access. [Any suggestions appreciated!]
>
>
>
> Thanks,
>
> Todd Gilbert
>
>