VBS and ASP

Topic

New Reply

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
objAccess.docmd.runMacro(“macUpdateID”)

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.
Thanks,
Scott

Reply | Quote

Replies

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:

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)), _
            adExecuteNoRecords
 
    'check whether there are any slips, and get function return value
    rstTimeRpt.Open "Q1SlipCount", conTimeRpt, adOpenForwardOnly, _
                    adLockReadOnly, adCmdStoredProc
    PrepopulateADO = rstTimeRpt.Fields("CountOfTSID").Value
    rstTimeRpt.Close
    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
conTimeRpt.Close
Set conTimeRpt = Nothing
StatusBar = ""
End Function

Hope this helps.

Reply | Quote

Thanks,
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:

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

Reply | Quote

Thanks,
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:

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

Reply | Quote

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:

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)), _
            adExecuteNoRecords
 
    'check whether there are any slips, and get function return value
    rstTimeRpt.Open "Q1SlipCount", conTimeRpt, adOpenForwardOnly, _
                    adLockReadOnly, adCmdStoredProc
    PrepopulateADO = rstTimeRpt.Fields("CountOfTSID").Value
    rstTimeRpt.Close
    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
conTimeRpt.Close
Set conTimeRpt = Nothing
StatusBar = ""
End Function

Hope this helps.

Reply | Quote