Re: Execute access module from package

#1

Can you create a macro in Access ?
Use the Access object model and call that from a piece of Active Script
task.
Similar to (In Excel)

Function Main()

'References to Excel
dim xl_app
dim xl_Spreadsheet

SET xl_app = CREATEOBJECT("Excel.Application")

SET xl_spreadsheet = xl_app.Workbooks.Open _
(DTSGlobalVariables("gv_str_SpreadsheetName").Valu e)

'Run the macro

xl_app.Run DTSGlobalVariables("gv_str_Macroname").Value

'Very important to clean up.
'if you make changes to a spreadsheet then XL will ask you if you want to
save
'it on closing so let's pre-empt it and save the workbook then close

xl_spreadsheet.Save

xl_spreadsheet.Close

xl_app.Quit
set xl_app = Nothing

Main = DTSTaskExecResult_Success
End Function

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ian" <none (AT) none (DOT) com> wrote

Quote:

Have searched google but either I am asking the wrong question or it's not
been asked before.

So could anyone tell me if it's possible to run some vba code in a module
in
an access database from a scheduled package?

Just in case there is a better way of doing what I am doing let me explain
why.

I have an access database that is linked to an exchange public folder. It
has to be access because our SQL server and Exchange server live on
different machines. I know if they were on the same machine I could use
ODBC link directly (the holy grail for me) from reading around this
subject.

I therefore created a link in access and wrote a vba sub to refresh it. I
then created an identical table in access and upsized it to SQL and wrote
another access vba sub to delete all rows from this new table and copy the
data from the refreshed exchange linked table. Works fine.

Then designed a DTS package (with the wizards help) to import the data
from
the unlinked table in the access database to the upsized table in SQL.
Put
a task to delete the contents to prevent duplication and everything works
OK
there as well. Sheduled it to run regularly.

Final part is to get the access to run the two vba routines on a scheduled
basis as well. I can do this with autoexec macro and windows task
sheduler
I think.

It occured to me however that it might be possible to get the scheduler in
SQL to run a vbscript/activex/something else routine that opened the
access
DB and called the two vba routines for me. This way I could link the task
with workflow on success before destroying the data in SQL tables and
re-importing. I have seen that VB can access VBA routines in excel so
thought it might be possible here.

Is this possible or am I barking up the wrong tree totally and should just
run the Access DB through windows scheduler?

#2

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

[snip]

Quote:

Function Main()

'References to Excel
dim xl_app
dim xl_Spreadsheet

SET xl_app = CREATEOBJECT("Excel.Application")

Ooops. Fell at the first hurdle. Woul;d have to install office on the
server and it isn't going to happen.

Thanks for the code though Alan. Can see uses for that in the future.

[snip]

--
Ian

#3

Yep you would need to install Excel on the server. MS will not support
server side unattended execution of Office either.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ian" <none (AT) none (DOT) com> wrote

Quote:

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:OKwNKH1WDHA.1268 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
[snip]
Function Main()

'References to Excel
dim xl_app
dim xl_Spreadsheet

SET xl_app = CREATEOBJECT("Excel.Application")

Ooops. Fell at the first hurdle. Woul;d have to install office on the
server and it isn't going to happen.

Thanks for the code though Alan. Can see uses for that in the future.

[snip]

--
Ian

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Execute access module from package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Execute access module from package - 08-05-2003 , 08:02 AM

Re: Execute access module from package - 08-05-2003 , 09:43 AM

Re: Execute access module from package - 08-07-2003 , 04:50 AM