Results 1 to 6 of 6

Thread: I give up!!!!

  1. #1
    Join Date
    May 2004
    Posts
    13

    Unanswered: I give up!!!!

    I have spent all day on this and finally resolve that I can't work it out by myself. I have a MS Access project (VB backend) that calls a stored procedure on SQL server. I continue to get the "Operation is not allowed when object is closed" error. Here is my code. I am certain it is something very simple but I can't see it anymore.

    Dim cnn As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim Adors As ADODB.Recordset

    cnn.ConnectionString = "Provider=SQLOLEDB; Data Source=bjcsql04; Initial Catalog=RiskMaster;User Id=xxxx; Password=xxxxx"
    cnn.Open

    cmd1.ActiveConnection = cnn
    cmd1.CommandType = adCmdStoredProc
    cmd1.CommandText = "sp_ReserveCurrent"
    cmd1.CommandTimeout = 90
    Set Adors = New ADODB.Recordset
    Adors.CursorLocation = adUseClient
    Adors.LockType = adLockOptimistic
    Adors.CursorType = adOpenStatic
    Set Adors = cmd1.Execute

    I do not pass a parameter to the stored procedure. That is the only thing different from any other projects I have created.

    Can someone help, please????

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Which line is throwing the error?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69

    SET cmd1.ActiveConnection = cnn ???

    Don't know if that's the cause for the error, but I think SET should be used there.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by chrisp 999
    Don't know if that's the cause for the error, but I think SET should be used there.
    So would this be the solution?
    Code:
    SET cmd1.ActiveConnection
    cmd1.CommandType = adCmdStoredProc
    cmd1.CommandText = "sp_ReserveCurrent"
    cmd1.CommandTimeout = 90
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    Code:
    SET cmd1.ActiveConnection = cnn
    .

    where cnn is the conection object as defined in the original question.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I may be wrong but I think you anly need to use SET if the variable is NOT dimensioned as NEW OR has not been previousley instatiated (ie Set ObjectVariable = NEW ObjectType)

    In this case it was dimensioned as new !!??

    But without knowing which line throws the error we don't even know which object is closed (ie connection, command or recordset)?


    MTB

Posting Permissions

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