x

Using Global Variables to Open a Database and Form

Hello, I have created a job which I pass two variables through as follows:

DTSRUN
/Sqh-sql2
/N"UnisoftVB"
/AgvAccessDatabase :8="Y:\InformationCopy.mdb"
/AgvAccessForm :8= "frmImportDataBackup"
/E

The "UnisoftVB" DTS contains the following ActiveX script:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Dim varAccessDatabase
Dim varAccessForm


Function PressButton()

varAccessDatabase = DTSGlobalVariables ("gvAccessDatabase").Value
varAccessForm = DTSGlobalVariables ("gvAccessForm").Value


 Dim objDB
 Set objDB = CreateObject("Access.Application")
 objDB.OpenCurrentDatabase(varAccessDatabase)
objDB.OpenForm(varAccessForm)
 objDB.CloseCurrentDatabase
 objDB.Quit
 PressButton = DTSTaskExecResult_Success

End Function

The form in the access database when opened runs a lot of VB (which I didn't write) and ultimately updates a table with up to the most recent data.

When I run the job it is successful but it happens far too quickly which made me suspicious that it wasn't working. The table doesn't contain the most up to date data.

I have checked my DTS by chaning the global variables and declaring them there instead and it does work.

I wondered if anyone could tell me why my Job doesn't seem to run my DTS correctly????

Please help Jo

more ▼

asked Feb 12 '10 at 12:30 PM in Default

Joeypotter gravatar image

Joeypotter
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Its a stab in the dark but you say the code runs when you open a form but the script above is called PressButton I know its only its name and it doesnt necessarily have to be associated with a button press but ...

more ▼

answered Feb 12 '10 at 01:01 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

It is only a name because the manual job that this is helping us with was pressing a button in an Access Database, that's the only reason it's called PressButton
Feb 15 '10 at 06:53 AM Joeypotter
OK, thats fine. so long as the code is relevant!! ;) Let me understand you right - the code above doesnt work but if you take out the global variable and submit static values for varAccessDatabase and varAccessForm it all executes correctly? If so then this looks like the Global variables are not getting set properly. Can you comment out the lines above that 'DO' stuff and just get them to messagebox/alert the var values after they have been set? This will show you whats in them. This wont show to screen unless you manually execute the job - a scheduled execution ignores messabox commands.
Feb 15 '10 at 07:25 AM Fatherjack ♦♦

I've been looking for guidance on getting it to use a msgbox. When I execute the job the box doesn't appear. Could you tell me the VB code to add the msg box you refer to?

Many thanks
Feb 15 '10 at 01:42 PM Joeypotter
I have added a 2nd answer so that the process is clearer than in these comments.
Feb 15 '10 at 03:54 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

OK, in a new SSIS package add a script task to the Control Flow tab. Set the language to Visual Basic (it may be Visual C# by default). Edit the task. Click the Edit script button. In there you will find a section of code that is Sub Main. Paste this after the comments but before the DTS.TaskResult .. line

Dim myVar As String 
        myVar = "My Varaible has a value"
        MessageBox.Show("Hello World" & vbCrLf & myVar)

Click File|Exit and then OK the dialogs until you get to the Control Flow editor. Press F5. You should get a message box come up with a message.

Now you need to get your Globalvariable value into a similar message box and you can check how your script is working.

more ▼

answered Feb 15 '10 at 03:53 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x85
x65
x26
x20

asked: Feb 12 '10 at 12:30 PM

Seen: 1510 times

Last Updated: Feb 12 '10 at 12:47 PM