Results 1 to 3 of 3

Thread: VBS and ASP

  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Atlanta, Georgia, USA
    Thanked 0 Times in 0 Posts

    VBS and ASP

    I created a small vbs script that runs some queries in a database.
    Right now I have the script in Windows scheduler to run every hour.
    What I was wondering is if I can place this code in an asp page that will run when the page loads.
    This way it would run the queries after they update fields in the database through the asp page instead
    of running every hour when it dosn't need to.
    Here is the code:

    Dim objAccess
    Dim strDBName

    Set objAccess = WScript.CreateObject("Access.Application")
    strDBName = "C:InetpubwwwrootRegionconfigConfiguration.mdb "
    objAccess.OpenCurrentDatabase strDBName

    Or is there a better way of doing this.

    The files are on our intranet so security issues shouldn be problem.
    I have limited asp knowledge but I'm a quick learner.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: VBS and ASP

    Is this on a web server? Microsoft recommends against automating Access on a web server; it has said that it just isn't reliable enough for that kind of use. The alternative way to code it would be to use ADO. I don't think ADO can run a macro, but if the steps in the macro all consist of queries, ADO can run those, one after the other, just like the macro feature does. Does this sound like something worth exploring? If so, you will find lots of ADO code examples in the Lounge, some here (more closely related to ASP), some under VB/VBA (more closely related to Office automation), and many on the Access board (contexts vary).

    Here's a sample of some VBA code that runs several queries in an Access database (mostly) without retrieving any records. VBScript does not use strongly typed variables, and has different error options, and of course you wouldn't have a StatusBar in an ASP page, but for the most your code would be pretty similar:
    <pre>Function PrepopulateADO(strDBName As String, strTK As String, _
    datStart As Date, dateEnd As Date) As Long
    Dim conTimeRpt As New ADODB.Connection
    Dim cmdTimeRpt As New ADODB.Command
    Dim rstTimeRpt As New ADODB.Recordset
    PrepopulateADO = 0

    With conTimeRpt
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & strDBName
    End With

    'run delete query to purge the temp table
    StatusBar = "Clearing last report data..."
    With cmdTimeRpt
    .ActiveConnection = conTimeRpt
    .CommandText = "Q0ClearSlipData"
    .Execute , , adExecuteNoRecords

    'run parameterized append query to populate the temp table
    StatusBar = "Gathering slips during your selected period..."
    'the following spec is from the SQL window for the Access query
    'PARAMETERS [Timekeeper] Text ( 255 ), [Start Date] DateTime,
    ' [End Date+1] DateTime; latter value is "+1" because Access
    'wants a DateTime value rather than a date; need to roll forward
    'a day to capture time through 11:59:59PM on the desired date
    .CommandText = "Q1GatherSlips"
    .Execute , Array(strTK, datStart, DateAdd("d", 1, dateEnd)), _

    'check whether there are any slips, and get function return value
    rstTimeRpt.Open "Q1SlipCount", conTimeRpt, adOpenForwardOnly, _
    adLockReadOnly, adCmdStoredProc
    PrepopulateADO = rstTimeRpt.Fields("CountOfTSID").Value
    Set rstTimeRpt = Nothing

    If PrepopulateADO > 0 Then
    'run update query to add client names to temp table, if
    ' valid match is found
    StatusBar = "Adding client names..."
    .CommandText = "Q2AddCliNick"
    .Execute , , adExecuteNoRecords

    'run update query to add matter names to temp table, if
    ' valid match is found
    StatusBar = "Adding matter names..."
    .CommandText = "Q3AddMatNick"
    .Execute , , adExecuteNoRecords
    End If
    End With

    Set cmdTimeRpt = Nothing
    Set conTimeRpt = Nothing
    StatusBar = ""
    End Function

    Hope this helps.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Atlanta, Georgia, USA
    Thanked 0 Times in 0 Posts

    Re: VBS and ASP

    Yes, They are all queries.
    I'll look into that(ADO) to modify what I already got working after a days worth of web searching.

    I was able to get it to work the way I wanted with this:
    Dim oWSH
    Set oWSH = CreateObject("WScript.Shell")
    oWSH.Run "wscript c:ScheduledScriptsupdate.vbs"
    Set oWSH = Nothing

    Thanks for your examples, that will give me a place to start.

Posting Permissions

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