February 26, 2007 at 11:55 pm
Hello, I just upgraded my Access database too use SQL server 2005 using the upsize wizard, Just wondering how I can migrate the following query too SQL server, where the sample number is referencing a control on the Access form.
SELECT [Sizing Samples].[Minus Size], [Sizing Samples].[Plus Size], [Sizing Samples].Result
FROM [Sizing Samples]
WHERE ((([Sizing Samples].[Sample Number])=[Forms]![Inbound Samples]![cmbSampleNumber]))
ORDER BY [Sizing Samples].[Plus Size] DESC;
I guess I would have too write a stored procedure which took in the parameter for the sample number and then run the select statement and return the value back to the code.Can someone help me with the syntax too write the stored procedure and also what i would need to do to call the sproc from the Access code.
In the form it would be something like
-- VB Code
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Dim sampleparam As String
sampleparam = cmbsampleNo.Text 'Gets value off the dropdown list
'Not sure how to call the stored proc and pass the parameter
--SQL STORED PROCEDURE
Create procedure sqInboundSampleNo
@sampleno, int
AS select
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT [Minus Size], [Plus Size], Result
FROM dbo.[Sizing Samples]
WHERE ([Sample Number] = @sampleno
ORDER BY [Plus Size] DESC )
GO
Unsure off the syntax off the stored procedure..
If someone can please help..thanks in advance
February 27, 2007 at 5:08 am
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sqInboundSampleNo"
cmd.Parameters.Append cmd.CreateParameter("@sampleno", adVarChar, adParamInput, 6, sampleparam)
Set rs = cmd.Execute
If Not rs.EOF Then
_MinusSize = rs.Fields(0)
_PlusSize = rs.Fields(1)
_ Result = rs.Fields(2)
End If
set rs = Nothing
Set cmd.ActiveConnection = Nothing
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply