Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8

    Question Unanswered: Automate Workgroup Secured DB (WAS DTS package using VBScript)

    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.Run("StartBatch1", 41)

    objAccess.Quit
    Main = DTSTaskExecResult_Success
    End Function

    How do I do that?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Is this workgroup security or some custom stuff?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8

    Smile

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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:
    Code:
    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\Office10\MSACCESS.EXE"
         ' Use the path and name of a secured MDB on your system
         dbs = "C:\Board\Access_Automation\With_WorkGroupSecurity\BE.mdb"
         ' Use the path and name of your mdw file
         workgroup = "C:\Board\Access_Automation\With_WorkGroupSecurity\Secured.mdw "
          
         user = "TheUser"           ' Use a valid username
         password = "ThePassword"  ' 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.Run(application, vbMinimizedFocus)
    On Error Resume Next
        For i = 1 To 5
            
            Set accObj = GetObject(, "Access.Application")
            
     Sleep 1
     accObj.Run ("TheSub")
            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
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... 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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8
    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.Run(application, vbMinimizedFocus)
    On Error Resume Next
    For i = 1 To 5

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

    Sleep 1
    accObj.Run "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

  7. #7
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I ran this as a vbs script for testing and just copied the lines in. Get rid of two lines:
    Code:
    CONST DTSTaskExecResult_Success = 1
    and
    Code:
    x = Main()
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    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.RunSQL 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.RunSQL SQLSTR
    z = 0
    GoTo RerunDTS
    Else
    GoTo ContinueDTS
    End If
    End If

    End Function

  13. #13
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8
    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.Run(application, vbMinimizedFocus)
    On Error Resume Next
    For i = 1 To 5

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

    Sleep 1
    accObj.Run "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

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2006
    Location
    Providence, RI
    Posts
    8
    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 !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •