Script to run Access Query, need to pass parameter

[Babscoole]

I currently have a process which returns archived files back to main storage, but it takes two steps. Run a vbscript to actually move the file with the name being entered by InputBox. Then open up an Access database and run an Append Query , which takes input by InputBox, to restore the file listing to a "live" file table.

Fairly simple so far.

To make life a little easier on the users of this process, I want to make this work from one spot, the vbscript. Right now I’m working on the access portion as a standalone script until it’s functional, then I’ll incorporate it with the move file script. I've got a simple script to (hopefully) call the query, but really don't know how to pass the file name through to the Access query. When the query is run from Access, the InputBox stores the name in [Case_Number_To_Return]. If it matters, all inputs are in the form of 6 digit numbers.


Option Explicit

Dim oConn
set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\Case_Files.mdb"

' executing query
oConn.Execute "exec RetPurgProc"

' Clean up
' oConn.Close
' oConn.Quit
'Set oConn = Nothing

Thanks,

 

[dm4ever]

Try something like this.

oConn.Execute "exec RetPurgProc YourInput"

or if you store it in a variable

strInput1 = "YourInput"
oConn.Execute "exec RetPurgProc " & strInput1

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[Babscoole]

I tried the variable method since eventually this will be populated by InputBox, and got an error that the variable is undefined.

Option Explicit
strInput1 = "333333"
Dim oConn
set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\Case_Files.mdb"

' executing query
oConn.Execute "exec RetPurgProc" & strInput1

' Clean up
' oConn.Close
' oConn.Quit
'Set oConn = Nothing


I then tried removing Option Explicit. This caused a different error: "...cannot find the input table or query 'RetPurProc333333'...". So now it's just concatenating the variable to the name of the stored procedure. Blah.

 

[dm4ever]

If you use Option Explicit you need to do this

Option Explicit

Dim oCon, strInput1

strInput1 = "333333"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\Case_Files.mdb"

' executing query
oConn.Execute "exec RetPurgProc " & strInput1

' Clean up
oConn.Close

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[Babscoole]

ok, made those changes and I'm back to "...cannot find the input table or query 'RetPurProc333333'...".

Maybe some more info will help. The query being called looks like this in sql view:

INSERT INTO Case_TIFF_Files ( CaseNumber, Client_id, Client_Case_Nbr, Insured_LName, Insured_FName, Debtor_Lname, Debtor_Fname, File_Name, Case_Descrip, Date_Entered, Forward_Atty_Code )
SELECT Purged_Case_TIFF_Files.CaseNumber, Purged_Case_TIFF_Files.Client_id, Purged_Case_TIFF_Files.Client_Case_Nbr, Purged_Case_TIFF_Files.Insured_LName, Purged_Case_TIFF_Files.Insured_FName, Purged_Case_TIFF_Files.Debtor_Lname, Purged_Case_TIFF_Files.Debtor_Fname, Purged_Case_TIFF_Files.File_Name, Purged_Case_TIFF_Files.Case_Descrip, Purged_Case_TIFF_Files.Date_Entered, Purged_Case_TIFF_Files.Forward_Atty_Code
FROM Purged_Case_TIFF_Files
WHERE (((Purged_Case_TIFF_Files.CaseNumber)=[Case_Number_To_Return]));

 

[monksnake]

You need to put a space between the stored procedure name and the parameter. I see the space in dm4ever's code, but not yours Babscoole:
dm4ever code:

oConn.Execute "exec RetPurgProc "  & strInput1

Babscoole code:

oConn.Execute "exec RetPurgProc"  & strInput1

<.

 

[Babscoole]

That got it. Thanks!!! I knew it had to be some little syntax thing.

 

[monksnake]

No problem

<.