Can a vbscript be called from an MS Access button or macro?

[mlev]

I have written a vbscript to take data from an MS Access database and export it to a text file. The script runs several queries and divides the output with headers. No problem with the script, but I had planned to have it run as a scheduled task.

My manager would prefer it run by the Help Desk through Ms Access 2007 -- for example, a "export data" button that calls the script. Can a VBscript be called from a button in MS Access? How would I set that up? Do Access Macros accept vbscript? (Not sure if this should be posted in vbscript or access, but starting here.) Script is below.

' This script runs a query on an Access database
' It pulls the most recent version of the local content database for the Blue Coat SG proxy devices
' It copies this output to a text file that the SG devices will download
' ---------------------------------------------------------------' 

Option Explicit

Dim db, TextExportFile, cn, rs, strSQL 
Dim fs, f, fso, s, a 
Dim strHeader 

' Identify source database and target file for export'
db = "C:\database\bluecoat-local.accdb" 
TextExportFile = "C:\database\bluecoat_list.txt" 

Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
 
' Identify Microsoft database provider for Access 2007'
cn.Open _ 
      "Provider=Microsoft.ACE.OLEDB.12.0; " & _
      "Data Source=" & db 
   
' Run the query to select the nocache category from the Data table'
strSQL = "SELECT * FROM nocache"  
rs.Open strSQL, cn, 3, 3 

' Write the query output to a text file'
Set fso = CreateObject("Scripting.FileSystemObject") 
Set f = fso.CreateTextFile(TextExportFile, True) 
strHeader = "define category nocache" 
a = strHeader & vbCrLf & rs.GetString  
f.WriteLine a  
f.Close 

' Append next category'

' Identify source database and target file for export'
db = "C:\database\bluecoat-local.accdb" 
TextExportFile = "C:\database\bluecoat_list.txt" 

Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
 
' Identify Microsoft database provider for Access 2007'
cn.Open _ 
      "Provider=Microsoft.ACE.OLEDB.12.0; " & _
      "Data Source=" & db 
   
' Run the query to select the nocache category from the Data table'
strSQL = "SELECT * FROM CGI"  
rs.Open strSQL, cn, 3, 3 

' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

Set fso = CreateObject("Scripting.FileSystemObject") 
Set f = fso.OpenTextFile(TextExportFile, ForAppending, True) 
strHeader = "define category CGI_bypass" 
a = strHeader & vbCrLf & rs.GetString  
f.WriteLine a  
f.Close

 

[mlev]

Found it:

Function Runfile()
Application.FollowHyperlink "C:\database\dataexport.vbs"
End Function

Unfortunately, it doesn't work because the VBscript needs access to the database that it can't get when the database is open. So, basically, I can't call that script from within the database. I think I'll try to code a VBA solution instead.