SQL Pass-Through to Oracle - passing parameters

[lewatkin]

ACC 2K using 8i Oracle Client ODBC

I am trying to create a form that allows a user to input 2 variables into an access form, click a button and that makes a table. I have not tackled that yet as I cannot get my parameters to work (this is actually the first time that I have had to do this so I am in totally new water). Anyone got any ideas?

Here is my query - when i take out the Forms... stuff and replace them with values, it works perfect. but i need for the user to be able to input the DC and the MFG_CODE.

SELECT *
FROM SHAREADM.V_SHARE_CI_FORECAST
WHERE DC='"||[Forms]![frmEnterCrit]![txtDCCode]||"' and MFG_CODE = '"||[Forms]![frmEnterCrit]![txtMfgCode]||"'

Thanks in advance for ANY assistance!!

Lee

 

[HarleyQuinn]

How about:

WHERE DC='" & [Forms]![frmEnterCrit]![txtDCCode] & "' and MFG_CODE = '" & [Forms]![frmEnterCrit]![txtMfgCode] & "'

The || is used as a concatenation character in SQL, the & is used to concatenate strings/values in VBA.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Sorry, that should have read:

WHERE DC='" [red]&[/red] [Forms]![frmEnterCrit]![txtDCCode] [red]&[/red] "' and MFG_CODE = '" [red]&[/red] [Forms]![frmEnterCrit]![txtMfgCode] [red]&[/red] "'"

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[lewatkin]

Sorry, but that did not work.

This works:

SELECT *
FROM SHAREADM_V_SHARE_CI_FORECAST
WHERE DC='RAL' And MFG_CODE='BAR';


But i need the flexibility of being able to change the RAL and BAR.

 

[lespaul]

what SQL did you try that didn't work?

What didn't work? Error Message? No records returned?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lewatkin]

ALL:

i fixed it....i built a solution using HTML DB. thanks in advance for your help...

leslie, to answer your question, it was not returning any records.

thanks again,

lee

 

[PNC]

Lewatkin,

Could you elaborate your solution, because I have the same problem and I thought that it wasn't not possible to pass a variable in a pass-through query.

Thanks,
PNC.

 

[lewatkin]

the solution was created in another software application provided by Oracle called HTML DB and pl/sql. it was not resolved with access. the only thing that i could find was to write a stored procedure that included the variables. well, since i had to do that, i just used Oracle's front end tool.

sorry i could not be of more help

lee

 

[HarleyQuinn]

PNC, can you post the code/SQL query you are trying to use???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[RobertT687]

Here is a technique I've used in both Access97 and Access2K to create a pass-thru query with dynamic parameters.

Public Sub BuildPassThru(MyParm1, MyParm2 (...)
Dim MyQuery As QueryDef
Dim ConnectStr As String
Dim SQLCmd As String

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "qryMyPassThruQuery"
Set MyQuery = CurrentDb.CreateQueryDef("qryMyPassThruQuery")

ConnectStr = "ODBC;DSN=MyDSN;UID='myUserId';PWD='MyPassword';LANGUAGE=us_english;DATABASE=MyDataBase"

SQLCmd = "EXECUTE MyDataBase.dbo.MyStoredProcedureName '" & myParm1 & ", " & myParm2 (....)

With MyQuery
.Connect = ConnectStr ' Must be first to let access know this is a pass-thru query
.SQL = SQLCmd ' The string you just built
.ODBCTimeout = 0 ' No Timeouts
.ReturnsRecords = True ' This sp returns something
End With
MyQuery.Close
DoCmd.SetWarnings True
End Sub

This is intended to run against SQL Server, but I think it should work against Oracle as well as long as the ConnectStr is correct for that DBMS.

 

[HarleyQuinn]

That's still using a stored procedure.
Why not just connect to Oracle using ADO and then pass the the variables into the SQL used to return the recordset at run-time?? That seems like a much easier way than has been described by any of the previous posts, and was what I was intending to lead onto in my first post...

Harleyquinn

---------------------------------
For tsunami relief donations