Pass Through Queries with parameter from form

[Page410]

I posted this to the Access queries forum but perhaps I should be posing the question to this group.

I'm looking write a pass through query in Access 2002 that will contain a parameter supplied by a form. I'm having some difficulty getting this to fire. Does anyone have any experience in doing this?

How can I do this without getting error #195
ODBC--Call Failed
[MERANT][ODBC Sybase driver][SQL Server]'FCalc' is not a recognized built-in function name.

So far, this is what I have:
SELECT DISTINCT dbo.clmsttus_hist_lmy_v.clm_num, dbo.clmsttus_hist_lmy_v.clm_sttus_cde, dbo.clmsttus_hist_lmy_v.clm_change_dte
FROM dbo.clmsttus_hist_lmy_v
WHERE dbo.clmsttus_hist_lmy_v.clm_sttus_cde = "IP" AND dbo.clmsttus_hist_lmy_v.clm_change_dte > 'USER DEFINED FUNCTION'

Any help would be appreciated.

 

[OOBIE]

You are passing a Function that is not recognize instead of an actual value.

Try this :

Resolve the function first on the form , capture the output and send it to your
SQL as a parameter.


Happy Cooding!

 

[Page410]

I already have the value that I am trying to pass on the form. I actually built the function to capture the date from the form in order to try to pass it to the server.
I'm trying to hit this table through an ODBC connection to a SQL Server and the syntax that you would use with the jet isn't going to work.
I need to find a way to pass the parameter in a manner that the the servers native language can understand.

Thank you

 

[SQLSister]

Have you tried creating the code in a stored procedure and then making the pass-through query just call the stored procedure with the appropraite parameters? This is what we do.

 

[Page410]

That would be great. Unfortunately, I am restricted to Access 2002 and don't have the ability to create stored procedures.

 

[SQLSister]

Here is how to do what I described above:

First write and debug the stored procedure that does what you want.

Then create the passthrough query including setting the connection string and giving it the name you will use in the VBA code. In this case the passthrough query might read:
Execute USP_CustRptSectionsIUO '00004041802', 1

Then create the VBA code on the event you want to run the code from (Usually on form load or on click). Example:
numProposalNumber = Forms!frmProposal!subfrmProposalGeneral!ProposalNumber
strSQL = "Execute USP_CustRptSectionsIUO '" & numProposalNumber & "', " & intReportNumber & ""
Set qrySections = db.QueryDefs("ViewRptSectionsIUO&quot
qrySections.sql = strSQL
(of course the variables in the above code were defined earlier in the code, but I figures you knew how to do that.)

Does that help?

 

[tlbroadbent]

Check out this link.

ACC2000: How to Simulate Parameters in an SQL Pass-Through Query

http://support.microsoft.com/default.aspx?scid=KB;en-us;232493

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.