Parameter Queries in Access Projects

Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

ThanksYou are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott

Originally posted by Sia
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

Thanks|||I know that its not easy but do you know of any sample that I can look at?

Thanks

Originally posted by hmscott
You are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott|||Consider this:

SQL Stored Procedure:

CREATE PROC spDoThis
@.Parm1 as Varchar(255),
@.Parm2 as Int

AS

UPDATE MyTable
SET Column1 = @.Parm1
WHERE ID = @.Parm2

ADO Code (in your form):

Private Function Update_Click()

Dim oConn, oComm

' Instantiate ActiveX objects
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")

' Set connection string
oConn.ConnectionString="Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security=False;" & _
"Catalogue=MyDatabase;" & _
"Server=MyServer"

oConn.Open

' Set the ADO Command to the name of the stored proc
Set oComm.ActiveConnection = oConn

oComm.CommandText = "spDoThis"
oComm.Parameters.Refresh

' Assign the values to the stored proc parameters from your form
oComm.Parameters("Parm1") = [MyForm].[MyText].[Value]
oComm.Parameters("Parm2") = [MyForm].[MyRecordID].[Value]
oComm.Execute

' Clean up ActiveX objects
Set oComm = Nothing
oConn.Close
Set oConn = Nothing

End Function

Notes:
1. This was written from the top of my head in a hurry (ie, don't nag me about syntax).
2. I have not tested this.
3. Your mileage may vary.

Hope this helps.

Regards,

Hugh Scott
Originally posted by Sia
I know that its not easy but do you know of any sample that I can look at?

Thankssql