Passing Variable off Form to SQL Pass Through Query

Passing Variable off Form to SQL Pass Through Query

Post by Jim Mar » Wed, 14 Mar 2001 01:10:28



How does one go about passing a value from a text box off of a form to a SQL
Pass Through query.

Tried something like the obvious, but no success




TIA
JIM

 
 
 

Passing Variable off Form to SQL Pass Through Query

Post by Let Me Through, I'm A Docto » Wed, 14 Mar 2001 01:26:02



> How does one go about passing a value from a text box off of a form to a
SQL
> Pass Through query.

> Tried something like the obvious, but no success




> TIA
> JIM

This is the easy way where cboFund is a combo box and acxAsofDate is a date
control.

    strFund = cboFund
    strAsOfDate = "'" & Format(acxAsofDate, "dd mmm yyyy") & "'"

    strSQL = "EXEC dbo.Report_Fund_Basic " & cboFund & "," & strAsOfDate
    DoCmd.RunSQL strSQL

The trickier way involves things like

    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    cmd.CommandText = "dbo.prc_update_tradar_auto"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh





    cmd.Execute , , Options:=adExecuteNoRecords

and so on which is much more efficient for repeated actions e.g. looping
through securities in a form and repricing them continuously from a
third-party data provider but you would be best to check out help files or
the Litwin, Getz, Gilbert Access 2000 Developer's Handbook Volume 2 if you
need to go into this further.

 
 
 

Passing Variable off Form to SQL Pass Through Query

Post by Jim Mar » Wed, 14 Mar 2001 02:30:04


How does access know what sql server??
Is your stored procedure imported into access??





> > How does one go about passing a value from a text box off of a form to a
> SQL
> > Pass Through query.

> > Tried something like the obvious, but no success




> > TIA
> > JIM

> This is the easy way where cboFund is a combo box and acxAsofDate is a
date
> control.

>     strFund = cboFund
>     strAsOfDate = "'" & Format(acxAsofDate, "dd mmm yyyy") & "'"

>     strSQL = "EXEC dbo.Report_Fund_Basic " & cboFund & "," & strAsOfDate
>     DoCmd.RunSQL strSQL

> The trickier way involves things like

>     Set rst = New ADODB.Recordset
>     Set cmd = New ADODB.Command

>     cmd.CommandText = "dbo.prc_update_tradar_auto"
>     cmd.CommandType = adCmdStoredProc
>     cmd.Parameters.Refresh





>     cmd.Execute , , Options:=adExecuteNoRecords

> and so on which is much more efficient for repeated actions e.g. looping
> through securities in a form and repricing them continuously from a
> third-party data provider but you would be best to check out help files or
> the Litwin, Getz, Gilbert Access 2000 Developer's Handbook Volume 2 if you
> need to go into this further.

 
 
 

Passing Variable off Form to SQL Pass Through Query

Post by Let Me Through, I'm A Docto » Wed, 14 Mar 2001 02:42:10



Quote:> How does access know what sql server??
> Is your stored procedure imported into access??

As you asked this question in the adp newsgroup I assumed you were using a
.adp database instead of a .mdb one. In the old world of .mdb the easiest
way would have been to create a standard pass-through query connected
through ODBC and then change the SQL text with a string containing the
stored procedure name and parameters, but that's for another newsgroup.
 
 
 

Passing Variable off Form to SQL Pass Through Query

Post by Jim Mar » Wed, 14 Mar 2001 03:10:57


Thanks:  Made it an adp and it worked like a champ.
I used to work with Access 97, but it looks like I'm going to have to check
out all the new features of Access 2K.
One question access doesn't treat all the objects as linked like in the old
days does it.  Or does it actually read the metadata from SQL and create its
own objects.





> > How does access know what sql server??
> > Is your stored procedure imported into access??

> As you asked this question in the adp newsgroup I assumed you were using a
> .adp database instead of a .mdb one. In the old world of .mdb the easiest
> way would have been to create a standard pass-through query connected
> through ODBC and then change the SQL text with a string containing the
> stored procedure name and parameters, but that's for another newsgroup.

 
 
 

1. Passing a variable to an SQL query import

I haven't followed this group for a while, so I apologize if this has been
answered recently, but...  Is there a way to pass a variable to the SQL
query that would drive an import into Filemaker (V. 5).  I can do a query
and import data manually, and I can script it to perform the same import
without dialogues.  But I don't want to run the same query every time.  Am I
missing something simple, or is FileMaker missing something useful?

Thanks in advance.

Rafe

2. How can i limit the SQL Server VMUsage?

3. passing parameters to a sql query from vb form

4. Graphs in FPW 2.6?

5. passing sql prompt variable to pl/sql block

6. Problem with Intersolve ODBC Driver

7. Pass a PL/SQL variable into SQL DEFINE

8. Pass global variable from form to form

9. MS Access (SQL) Project: passing from Form to Query

10. Creating Report off Pass Through Query

11. Passing Variables to Forms