Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88

    Unanswered: Pass Parameter from Access VBA to SQL

    I'm running Access 2000 with SQL Server and have run into problems using DAO. I have decided to switch to ADO but cannot figure out how to pass a parameter from VBA code to a SQL Stored Procedure. Also, not clear on whether I need to Set Comm1 once or each time I open a recordset. Please help.. Thanks.
    My code so far:
    Dim Comm1 As New ADODB.Connection
    Dim Rst1 As New ADODB.Recordset
    Dim Cmd1 As New ADODB.Command
    Set Comm1 = Application.CurrentProject.Connection
    Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = Comm1
    Cmd1.CommandText = "sp_Test"
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.Parameters.Append ??
    '? Cmd1.Parameters.Refresh
    '? Cmd1.Parameters(1).Value = XForID
    Set Rst1 = Cmd1.Execute()
    ...
    Rst1.Close
    Set Rst1 = Nothing

  2. #2
    Join Date
    Jul 2003
    Posts
    4

    I have been

    bashing my brains out on this exact same one for 48 hrs

    A newbie to ADO - I am 'missing the point' on this

    Any help gratefully required

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    using parameters in ADO and then touching that recordset more than once will (I think) make you have to reestblish the connection each time

    I could be wrong but I racked my brains out as well doing the same thing a couple of years ago and found out that my .connection was being dropped every time I passed a parameter

    the following link is various ado/sql/parameter issues

    http://support.microsoft.com/search/...false&numDays=

Posting Permissions

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