Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: 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?


    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Parameter Queries in Access Projects

    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

    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

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: Parameter Queries in Access Projects

    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

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Parameter Queries in Access Projects

    Consider this:

    SQL Stored Procedure:
    Code:
    CREATE PROC spDoThis
         @Parm1 as Varchar(255),
         @Parm2 as Int
    
    AS
    
    UPDATE MyTable
    SET Column1 = @Parm1
    WHERE ID = @Parm2
    ADO Code (in your form):
    Code:
    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?

    Thanks
    Last edited by hmscott; 02-04-03 at 22:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •