macros in stored procedures

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access SQL Stored Procedure: Can I call a Macro that I
create in Access as well?

How if yes?

alter procedure "sp1"

as

call macro some how

return
 
Hi,


nope, at least, not easily... ( a possible way is to reach the macro/VBA
code through automation through a dll that MS SQL Server would have to call
through some special mechanic inside the stored proc). If you look at it in
another way, a stored proc can be called by someone seeing the MS SQL Server
from another application, having no tie with your Access application. The
path to reach your Access application from that other application is not
evident and is not "built in" right out of the box.


That is not required that anyone can run your sp ? if YOU know when you
call that stored proc, and you (your app) is the only one to use the sp,
then why not "encapsulate" that call through some VBA call:

Public Sub MyCall( )
... call your macro here...
... next, call the stored procedure ...
End Sub


and use "MyCall" rather than calling directly the sp (if that is doable,
given your exact context).



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for a quick response. No wonder I could not find
much information during my hour plus long searching. I
was trying to automate the creation of a spreadsheet on a
network drive from a view. Of course, using macros (in
ACCESS 2000), the tranferspreadsheet command only allows
you to transfer tables, not views. Don't ask me why. So,
I had created a stored procedure to create a table from
the view, then to execute a macro to create the XL
spreadsheet. The entire reason for doing it in a SP was
to create a job on Enterprise Manager and schedule it.
(for those who are curious, I got the macro to execute the
SP and then create the network spreadsheet, but I couldn't
schedule a macro, could I?)

Bottom line is, I got it working an entirely different
way. I was playing around with my local "Scheduled Task"
Icon (I was going to run the macro from "Scheduled Task"
with a 1 min time limit) and got it to call the macro as a
shortcut. During that process, I came across as "Import
and Export Data". Never saw that before, and well, to
make a long story short, I created a DTS package to create
a XLS spreadsheet DIRECTLY from a view and scheduled on
the server. Case Closed.

This whole MS SQL Server is less than 2 weeks old for me,
and, I am learning alot about it. Thanks to all who post
and more importantly to those who answer questions.

Regards,

Doran George
 
Hi,


You can run a macro opening msAccess.exe through the command line /X
option. That command line can be supplied to the Task Scheduler.


Hoping it may help,
Vanderghast, Access MVP



(...)
 
Back
Top