Vbscript Return Access Function Variable

[Guest Ben Harper]

Hi there,

 

 

Runing phone line competition that will answer a call, then add a record in an access db, that includes a time stamp and some other VG module variables.

Then a bnscript module will need to call and n msaccess module that will process

that will return a variable based on the record mentioned above.

 

Q.

Is there a way to connect to a database, run a module, retrieve back the variables

I dont want to create a new instance of msaccess and close it again as i may have many calls running this script at once, the db state will need to remain unaffected.

 

Thanks

[SupportTeam]

What 'MSAccess module' is the VBScript calling? Is the VBScript just reading back data from a database or is it doing something else?

 

If you need to use VBScript to do some database work then maybe you should just do the data insertion from the same VBScript...

 

There are no problems with using MSAccess when running multiple lines and using the DB Query module in scripts running on each of the lines... the speed of data retrieval will not be as fast as with some other more 'industrial strength' databases but that's about it.

[Guest Ben Harper]

The module being called has yet to be written.

 

It will simply check on a record that will have been inserted at the begining of the call and depending on some conditions and variables will return a yes or no voiceguide.

 

Yes, I may do the insert from the same script, thanks.

 

The main question I have is,

have you ever come across a way of firing up an access module and retrieveing the results from a vb script?

In the same way that you would make a function call and retirve result variables if you were developing in access alone,

 

Thanks

[SupportTeam]

have you ever come across a way of firing up an access module and retrieveing the results from a vb script?

We're not too familiar with the MSAccess modules or how to access them form VBScript, sorry.

 

As this is more of a VBScript related question rather then a VoiceGuide related question you may want to look at VBScript related forums/help (or MSAccess related forums/help)

 

At the bottom of VG's Help file's section on "Run VBScript module" there are some links to VBScript related forums, see bottom of: http://www.voiceguide.com/vghelp/html/modVbs.htm

[Guest Ben Harper]

Thanks

[Guest Ben Harper (Ben@tacticsmarketing]

SOLUTION: IN CASE ANYONE STUMBLES ALONG THIS POST WITH A SIMILAR PROBLEM AT HAND,

 

 

I found 2 ways of doing this.

 

1) Access Msaccess via the application layer, with a vb script like this:

 

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "c:\testing.mdb"
objaccess.run "sayaname","David"        'sayname is name of function, david is paramter
'objaccess.visible = true
objaccess.quit
set objaccess = nothing

 

Which would access this code in c:\testing.mdb

 

Public Function sayname(ByVal name As String)
Dim text As String
text = "Hello" & name
msgbox text
End Function

 

SO I PASSED THE PARAMATER "DAVID" In and ran it.

But this was messy because it opens an instance of msaccess and uses VBA.

 

 

SO, the better way (for my needs at least, because I also wanted to also to return variables to vbs / voice guide) was to make a query that returned the values I wanted based on the parameters I was passing in from vbs / voice guide, and return them to vbs as a recordset.

 

2)

 Dim cnn  'As ADODB.Connection
   Dim cmd  'As New ADODB.Command
   Dim rst  'As New ADODB.Recordset
   Dim Var1  'promo no
   Dim Var2
   Dim Var3    'result from qry
   const myDb = "c:\testing.mdb"

   'variables from voice guide
   Var1 = 1    
   Var2 = 1
  
   Set cnn = CreateObject("ADODB.Connection")
   Set cmd = CreateObject("ADODB.Command")
   Set rst = CreateObject("ADODB.Recordset")
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& myDb 
   
'    MsgBox "dbState: " & cnn.State
   With cmd
      .ActiveConnection = cnn
      .CommandText = "qryTst2ParmQry"       'name of 2 parameter qry

      Set rst = .Execute(, Array(Var1, Var2), 4)  '4 == adCmdStoredProc
      If rst.BOF And rst.EOF Then           'no records found
         Var3 = ""                         '
      Else
         Var3 = rst(3)                     '
      end if          
      rst.Close
   End With
   cnn.Close

   Set cmd = Nothing
   Set rst = Nothing
   Set cnn = Nothing

'can movenext in recordset to get subsequent records returned by the query, i think, havent tested it though   
'Assign to Voice Guide variable for subsequent use (vg result return)

 

Or you can substitute the 4 in

Set rst = .Execute(, Array(Var1, Var2), 4) '4 == adCmdStoredProc

For a 1 or a 2 (it think these values are for sending an sql string and an unknown execution, which is which I don’t know but they both work) and then replace

.CommandText = "qryTst2ParmQry" with

.CommandText = YOUR SQL STRING

 

Or I suppose you could just call some function and pass in and out the parameters.

What ever, it makes interaction with msaccess a billion times more useful than if you only had the query module.

 

BTW, I am an end user (and a begginer at that) so if this code has any serious flaws and ruins everything, dont blame voiceguide, Thanks