is using 'black pr' to moderate forums moral?

[aaron.kempf]

MOS?

isn't that basically 'do you know how to open excel and what the
extension is'?

i've scored in the 90th percentile in access for years and years anyone
know a free online mos test for excel??

 

[aaron.kempf]

i just think that there should be tiered certfications== like in the
programming world.

not one; but a half dozen levels of certification.. even industry
specific..

 

[aaron.kempf]

i want to know who is an 'excel architect' and who is a tweaker

 

[aaron.kempf]

god i swear i answered that q yesterday about multiple adp files

gave 4 easy ways to do it??

 

[dbahooker]

I swear I didn't see that post i think that im getting senile as my
brain is getting filled with these silly XL macros again

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

god i swear i answered that q yesterday about multiple adp files

gave 4 easy ways to do it??

You gave 1 using RunApp. Maybe you should check.

And about that RunApp way to do it, doesn't that presume those macros
already exist? How do you print reports in multiple ADPs if there
aren't any pre-existing macros to do it?

 

[dbahooker]

you keep all your stuff in one file for starters.

I've had several ADP files with several hundred reports in them before.

that's part of the beauty of ADP.. you can have 100 different reports;
slicers and dicers.. in one 2mb file instead of 100 different
spreadsheets

you make a macro in the destination ADP.

1) new macro in the destination ADP
2) select 'OpenReport' as the first step
3) at the bottom choose the report name you want to open.
4) (optional) the default view is to 'print' you can also have those
preview if you would prefer
5) (optional) enter a filter condition and / or a where condition
6) rinse and repeat step 2-6 for any other reports you want to automate
7) save the macro in Access.
8) close out of the 2nd access instance
9) RunApp the name of the CommandLine Argument to print your reports;
in an exteranl adp - without ANY programming.

you can use this for your month-end batches for example.

you can use this to print to a fax server.

i've written automated fax solutions using this methodology without ANY
friggin programming.

I prefer to output reports to snapshot and then have an async program
run the snapshot viewer to print to the fax machine.

In order to do that in Excel; you would need to purchase Adobe Acrobat
and you would also need to program.

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

I swear I didn't see that post i think that im getting senile as my
brain is getting filled with these silly XL macros again

....

Nah. Pre-existing condition.

 

[Harlan Grove]

(e-mail address removed) wrote...

you keep all your stuff in one file for starters.

....

Not always allowed. Reread my example: one ADP for a financial
reporting database, another for a payroll database. In most companies,
aside from the DBAs, only HR people have any access to payroll
databases, and other people would have access to financial reporting
databases. But assume you're a DBA who's received a rush request from a
SVP for one report from each of these SEPARATE databases. How do you
print those reports with one command?

 

[dbahooker]

you have a macro.

or you have a job send these reports out.

or you have a sproc that fires other sprocs.

having SQL Server send an email with a report?

that is a single line of TSQL.
xp_sendMail has an argument where you can email the results of a
sproc.. i mean-- most reports are a single sproc anyways; so this comes
in pretty convienent

But from the VBA Side-- how would I do this??

Dim Acc as new Access.Application
Dim AdpDest
Set AdpDest = Acc.OpenAccessProject
AdpDest.Docmd.OpenReport

that would just take me a couple of seconds to bust out.

i'm late for a lunch date; i swear that there is a completely one macro
way to do it..

I mean shit you can do ANYTHING with runCommand.. i mean shit.. i'll do
it with SENDKEYS lol

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

you have a macro.

....

As long as the macros exist, fine. It can be done by running another
instance of Access which runs the report in the other database.

Note that RunApp is a security hole. If it can run any outside
executable, it can also run malicious or destructive programs.

Your other approaches aren't applicable to ad hoc report production.

 

[dbahooker]

ad hoc reports?

there's only one flavor of adhoc reports that matter.. it is called
Olap and Office Web Components.

Sub-Second; it's response time is FABULOUS.
and it allows for custom functions; etc.. and the functions aren't VBA;
they're MDX functions / expressions

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

ad hoc reports?

'Ad hoc' meaning for a limited, specific, implicitly not intended to be
repeating need.

there's only one flavor of adhoc reports that matter.. it is called
Olap and Office Web Components.

....

Perhaps for you.

So you can't answer the question how to generate one report in each of
two separate ADPs when there are no pre-existing macros in them to
produce the reports. Guess that means it'd be the same manual process
as is needed in Excel printing worksheets in separate workbooks when
there are no macros to do it.

 

[aaron.kempf]

dude you're such a corn-holer

I just described FOUR WAYS TO DO IT

and my 'ad-hoc' in Olap is about 10 times better, faster, more powerful
and more dynamic than anything you're ever seen

now you're saying 'generate' reports
earlier we were talking about reporting on it.

for starters.. if they were 2 databases on the same server?

Select Orders.OrderDate, OrderDetails.*
from northwind.dbrders INNER JOIN Northwind.dbrderDetails ON
Orders.OrderID = OrderDetails.OrderID

If I wanted to refer to information from 2 databases on the same
server????
I mean seriously here

Select Employees.EmployeeID, Employees.EmailAddress, Salary.Rate
from payroll.dbo.Salary INNER JOIN Employees.dbo.Employees ON
Salary.EmployeeID = Employees.EmployeeID

If I wanted to refer to information from 2 databases on the different
servers????
I mean seriously here. I'd ave linked servers set up previouly.
sp_addLinkedServer 'SERVER2'

Select Employees.EmployeeID, Employees.EmailAddress, Salary.Rate
from Server1.payroll.dbo.Salary INNER JOIN
Server2.Employees.dbo.Employees ON Salary.EmployeeID =
Employees.EmployeeID

that's from the DATA SIDE.

From the application side?

I've already listed 4 different ways to do that

hit the button on your keyboard labelled 'Page Up' ok??

 

[aaron.kempf]

I was primarily asking about doing things sequentially-- fire off a
bunch of reports without sitting around and pressing print by hand--
like for a month-closing for example

1) out of the box the description for RunApp states "Starts another
Windows or MS-DOS application, such as Microsoft Excel or Word. The
specified application then runs in the foreground and the macro
continues to run"


Here are the details for 'RunApp'

------------------------------------------------------------------
RunApp Action
You can use the RunApp action to run a Microsoft Windows-based or
MS-DOS-based application, such as Microsoft Excel, Microsoft Word, or
Microsoft PowerPoint, from within Microsoft Access. For example, you
may want to paste Excel spreadsheet data into your Access database.

Setting
The RunApp action has the following argument.

Action argument Description
Command Line The command line used to start the application (including
the path and any other necessary parameters, such as switches that run
the application in a particular mode). Enter the command line in the
Command Line box in the Action Arguments section of the Macro window.
This is a required argument.

Remarks
The application selected with this action loads and runs in the
foreground. The macro containing this action continues to run after
starting the application.

You can transfer data between the other application and Access by using

the Microsoft Windows dynamic data exchange (DDE) facility or the
Clipboard. You can use the SendKeys action to send keystrokes to the
other application (although DDE is a more efficient method for
transferring data). You can also share data among applications by using

Automation.

MS-DOS-based applications run in an MS-DOS window within the Windows
environment.

In Windows operating systems, there are a number of ways to run an
application, including starting the program from the Windows Explorer,
using the Run command on the Start menu, and double-clicking a program
icon on the Windows Desktop.

You can't run the RunApp action in Microsoft Visual Basic. Use the
Visual Basic Shell function instead.
-----------------------------------------------------------
so I would have a macro in each destination adp.. and RunApp for the
path to these to do with out of the box without doing anything funny.
Out of the box without writing any code

If you want more options:

2) I could schedule these reports using SQL Agent-- which comes with
MSDE: and have these sent out on a scheduled basic.. right-click
SCHEDULE would be all it would take.. the SQL Job Scheduler is _QUITE_
friggin powerful.. nothign similiar to that inside of MS Excel now is
there??

3) have a DTS package that is running in the background that runs 100
processes at the same time. I use that trick for parsing text all the
time. Does Excel include a DTS tool? LoL.

4) or if worst came to worst; I could just open multiple instances of
Acces by hand; if it gives you a hard time you can put a shortcut to
MSACCESS.exe in the sendto dir and that will get through the trouble.

Do you know what I mean-- when you have one LONG-running Excel macro
and you double-click on a spreadsheet.. and it doesn't open Excel since

'Excel is busy'

well putting a shortcut to EXCEL.exe in your sendto directory would fix

that problem as well

5) there is some shellEx or some other function / API call where you
can specify whether you want to wait or not.. I can't find it right now

and I dont really need it. Anything i do through the command line; most

of it runs from the db server xp_cmdShell 'dir c:\' etc.. get the
results from a dos command and keep them in a database? xp_cmdshell
makes it super-duper easy.

-Aaron

 

[aaron.kempf]

and if you're talking about creating reports?

yeah its' simple automation

Dim rpt as new Report
rpt.recordSource = "select * from vwMyOrders"
yadda.. yadda..yadda

and again

my VBA isn't DANGEROUS like your VBA

your VBA is ridiculous; i mean..
emailing documents around???? with DATA in them??

it's ridiclous.

having 10 different copies of the same spreadsheet?

the most ridiclous thing i've ever heard

 

[Harlan Grove]

(e-mail address removed) wrote...
....

I just described FOUR WAYS TO DO IT

....

Let's see. In

http://groups.google.com/group/microsoft.public.access/msg/f052a40437afcd8e

you mentioned running a separate instance of Access using a RunApp
macro. That's one, but that's all you mentioned in that response. Then
in

http://groups.google.com/group/microsoft.public.access/msg/a041ee2fa4522719

your #2 would be another way. Would any user be able to schedule such
tasks? If not, not general and not too useful for anyone not a DBA. If
so, unwise if any user could add arbitrary tasks to the scheduler. #3
using DTS (meaning Data Transformation Services?) seems to be a method
for running multiple processes. Maybe it is, but Windows itself is
multitasking, so it's likely launching multiple Access sessions
directly from Windows would be more efficient. Even so, unless there
were macros in place to produce the reports, this would just be a
manual process in separate instances of Access. You can do the exact
same thing with Excel. Your #4 is yet another manual approach involvong
mutliple Access instances, so no different than in Excel. Finally, your
#5 is using scripting - maybe not VBA per se, but no different than
using VBA in effect, and just like using VBA or scripting to handle the
comparable task in Excel.

So you've provided maybe two ways to do this in Access or SQL Server
that have no Excel equivalents. The first requires pre-existing macros,
and the second requires permission to schedule server tasks. All the
other ways are variations on manual processing with multiple processes
or scripting.

 

[Harlan Grove]

(e-mail address removed) wrote...

and if you're talking about creating reports?

yeah its' simple automation

Dim rpt as new Report
rpt.recordSource = "select * from vwMyOrders"
yadda.. yadda..yadda

and again

my VBA isn't DANGEROUS like your VBA

....

Really? Why?

Also note that with Hotmail at least the warning provided when
attaching MDB or ADP files to e-mails is, "The file xyz.mdb(# MB) may
be unsafe. [...]" Unsafe! An MDB file? Why would Microsoft say an MDB
file might be unsafe? Gee, I wonder. It continues, "Many recipients
would not be able to open these attachments. [...]" Really? Many?! As
in MANY people don't have Access? Imagine that! The message is the same
for ADP files. Note that it's still possible to send them, there's only
a warning.

Attaching XLS files doesn't trigger the same warning. There could be
different reasons for this. Least likely is that Microsoft itself
considers XLS files less of a threat than MDB or ADP files. More likely
is that it's just boilerplate accompanying the fact that Hotmail
doesn't support MIME types for MDB or ADP files. That is, XLS files can
be opened directly from e-mail, but neither MDB nor ADP files can.
However, Hotmail DOES allow sending them and downloading them (just
tested and confirmed).

Back to security, Access VBA is just as dangerous as Excel VBA. The
only difference is that there may be fewer ways to trigger automatic
VBA code execution in Access. On the other hand, Access macros are NO
GUARANTEE of security. If Access macros can run arbitrary outside
applications using RunApp, that'd mean RunApp could be used to run
destructive programs such as

RunApp "C:\WINNT\System32\CMD.EXE /c rd C:\DOCUME~1 /s/q"

wouldn't it? It does, in fact. I just tested it (though not on
C:\DOCUME~1).

 

[aaron.kempf]

Harlan

of course it requires permissions

I described 5 ways to do it Harlan.

1) RunApp - yeah macros - simple single line, multiple choice macros--
big friggin deal
2) scheduled jobs - some users can do this; it's easy to keep secure.
it's easy to give permissions to certain functionality. Can Excel give
different permissions to different users? NOT REALLY LOL
3) 'but windows iteself is multitasking' gag me with a spoon. It's
easy to configure how many threads are running at the same time; etc.
I just think that it's funny -- you're saying 'it's not a feature just
because windows does the hard work'? That's the stupidest thing i've
ever heard of. Can Excel do multi-threading apps AT ALL? I can parse
10 or 100 instances at the same time.
4) Launching multiple instances of Access or Excel? I thnk that is too
technical for you lol
5) 4 part server.database.owner (aka schema in 2005).object

you forget -- the bottom line-- is that Excel VBA is inherently
insecure... and Access is inherently secure.

I'll find that posting i gave you

from the horses mouth

 

[dbahooker]

yes Access Macros can be mis-used.. of course they can
that is why Access prompts you about sandbox mode when you open it.

but the sheer count of threats? Excel VBA vs Access VBA?

there are 1,000 Excel VBA VIruse for every Access VBA Virus out there.

Don't bother to argue with the authority on the subject.

Access VBA _ISNT_ as dangerous as Excel VBA from an architecture
standpoint; and also from the standpoint that there are 20 Macro Virus
for Access and 20,000 for Excel.

and im not sure I agree with your findings on MDB and ADP; and whether
you can email them.

Did you email FROM HOTMAIL TO HOTMAIL?
Did you use the Outlook Connector for sending hotmail through outlook??

Did your files have VBA in them?
Did they have macros?

(e-mail address removed) wrote...

and if you're talking about creating reports?

yeah its' simple automation

Dim rpt as new Report
rpt.recordSource = "select * from vwMyOrders"
yadda.. yadda..yadda

and again

my VBA isn't DANGEROUS like your VBA

...

Really? Why?

Also note that with Hotmail at least the warning provided when
attaching MDB or ADP files to e-mails is, "The file xyz.mdb(# MB) may
be unsafe. [...]" Unsafe! An MDB file? Why would Microsoft say an MDB
file might be unsafe? Gee, I wonder. It continues, "Many recipients
would not be able to open these attachments. [...]" Really? Many?! As
in MANY people don't have Access? Imagine that! The message is the same
for ADP files. Note that it's still possible to send them, there's only
a warning.

Attaching XLS files doesn't trigger the same warning. There could be
different reasons for this. Least likely is that Microsoft itself
considers XLS files less of a threat than MDB or ADP files. More likely
is that it's just boilerplate accompanying the fact that Hotmail
doesn't support MIME types for MDB or ADP files. That is, XLS files can
be opened directly from e-mail, but neither MDB nor ADP files can.
However, Hotmail DOES allow sending them and downloading them (just
tested and confirmed).

Back to security, Access VBA is just as dangerous as Excel VBA. The
only difference is that there may be fewer ways to trigger automatic
VBA code execution in Access. On the other hand, Access macros are NO
GUARANTEE of security. If Access macros can run arbitrary outside
applications using RunApp, that'd mean RunApp could be used to run
destructive programs such as

RunApp "C:\WINNT\System32\CMD.EXE /c rd C:\DOCUME~1 /s/q"

wouldn't it? It does, in fact. I just tested it (though not on
C:\DOCUME~1).