I have the follwoing ActiveX task in DTS to run a function in a Microsoft Access database. The Access database now has security added to it and I need to be able to login and run a function.
The following is my code;

Function Main()
Set objAccess = GetObject("\\file-serv\p\mis\licadmin\Lic_app.mdb")

X = objAccess****n("StartBatch1", 41)

objAccess.Quit
Main = DTSTaskExecResult_Success
End Function

How do I do that?

Hi

Is this workgroup security or some custom stuff?

pootle flump ur codings are working excelent.

I am not sure. I was told that the Microsoft Access database out on the file server had security added within the database itself. So, if somebody goes to open the database, it will ask for a usernamr and password.

I hope this answers your question.

I basically need to create a DTS package that is able to connect to the database and run a function within the database. Then I can set this up to run once a night.

I am a DBA learning to be a developer, as well. I have never used VBScript, ActiveX, XML, etc. I have only used T-SQL.

Any help would be appreciated.

Ok - you'll need to clarify with the guy precisly what security they have enabled.

You can have code based, user defined security (in this case you will need to work very closely with the guy who created it).
Or work group security in which case you need the location of the mdw file (you can find that in the target of the shortcut that you will now need to use to open this database).
Or a database password.

On what you've said I would bet on the second option.

I've played around with this code:
http://support.microsoft.com/kb/192919
and produced this:
It wants some cleaning up. You also need to enter your Access application, database and mdw locations as well as change the procedure name.

I've also bodged a sleep function to save creating a COM object for the Sleep API call.

HTH

pootle flump ur codings are working excelent.

... and you'll need to change the username and password too. Which means they will have to be stored as plain text on the server unless you know some cunning encryption\ decryption method.

BTW - what does the access proc actually do?

pootle flump ur codings are working excelent.

Okay, we are using workgroup security.

I used this code in my ActiveX Script Task in my DTS package. I change all of the information accrodingly. It parses fine, but when I execute the step, I receive the following:

Package Error

Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Name redefined: ' DTSTaskExecResult_Success'

Error on Line 0

The code used is as follows (I did use a valid username and password):

CONST DTSTaskExecResult_Success = 1
const vbMinimizedFocus = 2
z = main
Function Main()

Dim accObj, Msg
Dim application, dbs, workgroup
Dim user, password, cTries
Dim i, x
Dim Shell

' This is the location of my copy of access... change to your version.
application = "C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"
' Use the path and name of a secured MDB on your system
dbs = "\\file-serv\p\mis\licadmin\lic_app.mdb"
' Use the path and name of your mdw file
workgroup = "\\file-serv\p\mis\workgroup\develop.mdw "

user = "username" ' Use a valid username
password = "password" ' and correct password
Set Shell = CreateObject("WScript.Shell")
application = Chr(34) & application & Chr(34) & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34)
x = Shell****n(application, vbMinimizedFocus)
On Error Resume Next
For i = 1 To 5

Set accObj = GetObject(, "Access.Application")

Sleep 1
accObj****n "StartBatchTest", 1
If Err.Number = 0 Then
Exit For
Else
Sleep 1
Err.Clear
End If

Next

If i = 5 Then
MsgBox "Problem with Access opening - exited function"

Exit Function
End If

' Turn off error handling
On Error GoTo 0
' You can now use the accObj reference to automate Access
accObj.CloseCurrentDatabase
accObj.Quit
Set accObj = Nothing
Main = DTSTaskExecResult_Success
End Function

Private Sub Sleep(S_ToSleep)
dim Start
Start = DATEADD("s", S_ToSleep, Now())
Do While Start > Now()
Loop
End Sub

I did change the username & password to a valid value.

The function in Access just writes a line to a text file for now.

Once I can get the DTS to be successful in connecting and running it, it will be used to run a function that runs a process.

Currently the developer leaves her PC running overnight, so that the process can run in the middle of the night.

We want to automate it.

Ah - I ran this as a vbs script for testing and just copied the lines in. Get rid of two lines:
and

pootle flump ur codings are working excelent.

I took this on because I like automation challenges but I can't help but feel this is "I have a hammer - there's a nail" kind of problem. Are you sure this needs to be a dts calling an activeX automating an access db that does x and y and z?

BTW - if this thread is not in the SQL Server forum next time you look for it it is because I moved it to the Access forum

pootle flump ur codings are working excelent.

I have to start by saying "Thank you" for teaching me new things.

Basically, there is a Microsoft Access application that takes care of software license administration. The company I work for sells software, support and licensing to its customers. The administrator of this system has a nightly process that has to run. She has to leave her PC up and running in order for the job to run.

We are trying to make use of SQL Jobs to automate this process. My boss tried to do this in the past without success. He used DTS with an ActiveX Script.

I am in my 3rd week at this new job and was asked if I could look into automating this process with DTS. If there is a better way, I am open to suggestions.

On another note, I took out the 2 lines you asked me to and here are the results:
1 - I receive a Micrososft Access Security Warning saying that "Unsafe expressions are not blocked. Do you want to block unsafe expressions?"
I clicked the NO button.
2 - a few seconds later, I receive a package error stating that ActiveX Scripting encountered a runtime error... I clicked OK.
3 - After #1 above, I was told that Microsoft Jet 4.0 Service Pack 8 or later must be installed. I am being warned that it may not be safe to open the .mdb if it contains expressions that were intened to harm my conputer.
4 - After I get by all of these, I get an error on line 48, which is "Loop"

Then Access does open to the main form within the application and I can do things with it. I will now need to see my boss and the Access developer, but I do not know if anything needs to be cleaned up to get rid of the above issues.

Ah - Access 2003 huh? Go to (from memory - I am an XP boy)
Tools -> Security -> Macro Security -> set the security to low.
There is a programmatic way to do this during automation (believe it or not - makes a mockery of the security) but I would have to dig this out.

I'm moving this thread to the Access forum too as it isn't really a SQL Server issue.

BTW - are you familiar with Windows Scheduler?

pootle flump ur codings are working excelent.

Had to do the same thing in access with an on demand run of DTS.
However I would suggest that you use the SQL schedulare. Windows Schedular, Or Automate if you have access to these.


Here is how I accomplished this.

I had to have execute permissions a couple of SP's (i cannot remember which ones)
SEARCH FOR THIS IN ACCESS FORUM opackage.Execute
**************************************************
Public Function RunDTS(ByVal IDKey As Integer, ByVal SourceFile As String, ByVal DestinationFile As String, _
ByVal DTSPackage As String, ByVal SP_Import_Cube As String, _
ByVal SP_Source As String, ByVal SP_Destination As String, ByVal Imp_TableName As String)


Dim response
Dim SQLSTR As String
Dim x As Integer, y As Integer, z As Integer, dtsresult As Integer
Dim initRecCount As Integer
Dim opackage As New DTS.Package
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

SQLSTR = "select count(*) from " & Imp_TableName
rst.Open SQLSTR, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
z = rst.Fields(0).Value
rst.Close

On Error GoTo ErrorHandle

'Run DTS from select DTS from tblClient_Input_Form where LoadSS_Name = '" & SourceFile & "'"
opackage.LoadFromSQLServer Server, "userid", "password", DTSSQLStgFlag_Default, "", "", "", DTSPackage, 0
'opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

RerunDTS:
opackage.Execute

ContinueDTS:

opackage.UnInitialize
Set opackage = Nothing

SQLSTR = "Update dbo.tblClient_Input_Form Set RuntoImport = 0 " & _
"where ID = " & IDKey

DoCmd****nSQL SQLSTR
Exit Function

ErrorHandle:
If z = 0 Then

response = MsgBox("You have duplicate key information in the spreadsheet " & SourceFile & " Please fix prior to attempting to load agian", vbOK)
Else
response = MsgBox("You are attempting to load duplicate data for " & Imp_TableName & Chr(13) & Chr(10) & _
" All data in this table will be deleted! " & Chr(13) & Chr(10) & _
" Do you wish to Continue? ", vbYesNo)

If response = vbYes Then
SQLSTR = "delete from " & Imp_TableName
DoCmd****nSQL SQLSTR
z = 0
GoTo RerunDTS
Else
GoTo ContinueDTS
End If
End If

End Function

I did not change anything since except that we installed Microsoft JET... and the certificate for security or whatever it was asking for.

Now the code worked and went through to dompletion and said "Successfully executed step".

So, the developer took the .mdb and copied it to another folder for further testing. She changed the "StartBatchTest" object within that .mdb to write out the files that happen on a nightly basis to a test area. I changed the script to point to the new .mdb.

Now when I run execute the step, it creates the files with the information in it. However, I get the follwoing DTS package error in the end...
"ActiveX Scripting encountered a Run Time Error during the execution of the script.

-----------------------------
const vbMinimizedFocus = 2

Function Main()

Dim accObj, Msg
Dim application, dbs, workgroup
Dim user, password, cTries
Dim i, x
Dim Shell

' This is the location of my copy of access... change to your version.
application = "C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"
' Use the path and name of a secured MDB on your system
dbs = "\\file-serv\p\mis\licadmin\nightlyjobs\lic_app.mdb"
' Use the path and name of your mdw file
workgroup = "\\file-serv\p\mis\workgroup\develop.mdw "

user = "username" ' I use a valid username
password = "password" ' and the correct password
Set Shell = CreateObject("WScript.Shell")
application = Chr(34) & application & Chr(34) & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34)
x = Shell****n(application, vbMinimizedFocus)
On Error Resume Next
For i = 1 To 5

Set accObj = GetObject(, "Access.Application")

Sleep 1
accObj****n "StartBatchTest", 1
If Err.Number = 0 Then
Exit For
Else
Sleep 1
Err.Clear
End If

Next

If i = 5 Then
MsgBox "Problem with Access opening - exited function"

Exit Function
End If

' Turn off error handling
On Error GoTo 0
' You can now use the accObj reference to automate Access
accObj.CloseCurrentDatabase
accObj.Quit
Set accObj = Nothing
Main = DTSTaskExecResult_Success
End Function

Private Sub Sleep(S_ToSleep)
dim Start
Start = DATEADD("s", S_ToSleep, Now())
Do While Start > Now()
Loop
End Sub

That's not a terribly useful message eh?

Try creating a new text file. Paste the original function code (with the x = Main() etc) into it. Change the extension to .vbs. Double click. What happens? (basically we are removing DTS from the equation. Once again I think this chain is too long and each link is just another thing that can go wrong).

BTW - you didn't answer re windows scheduler - did you consider that instead of DTS?

pootle flump ur codings are working excelent.

I am familiar with Windows Scheduler. I have used it once about 3 years ago.
What would I run using Windows Scheduler?

I created a text file with the entire script. Then I changed the extension to .vbs. I need to create an association, but I do not have VB installed on my PC! Is there anything else I can associate it with?

Just when I thought I was almost there !