Solved

ADO Write Conflict - Bound Access Form

Posted on 2004-10-25
1,276 Views
Last Modified: 2008-03-17
Hi there everyone,

    I ran into a problem and can't seem to find any help in EE on this issue.

    I have an Access 2003 Database using a SQL Server 2000 backend.  There are no tables or queries in the Frontend, only forms and code (using ADO to do everything)

   There is a form that calls a stored proc when it opens.  The stored procedure returns 3 recordsets and I populate two combo boxes with two of them and then bind the form (in continuous forms view) to the other recordset.  The data displays perfectly and I can make a change (operative word being "a")  to a record via the form.  I added a save button that saves the current record using

Docmd.runcommand accmdsaverecord

Here in lies the problem.  If I attempt to make another change to same record after clicking the save button, I get a write conflict error.  I'm opening the recordset with these properties set.

     With rstTemp
        .CursorLocation = adUseServer
        .LockType = adLockOptimistic
        .CursorType = adOpenDynamic
    End With

   The recordset that is bound to the form does have an inner join to get some supporting information from another table.

   Can someone shed some light on why I can't edit the same record twice?  I don't want to go to SQL Server and get the recordset again unless I have to.  

Thanks.
Mike
0
Question by:Data-Man
    30 Comments
     
    LVL 34

    Expert Comment

    by:flavo
    Heya Mike!

    Im not tooo sure about these things as you know, but don't you need to update the rs directly
    ie
    rs.find or seeks or whatever to get to the record
    rs("myField") = Me!txtMyText
    rs.update

    rather than the standard Docmd.runcommand accmdsaverecord

    ????
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    the recordset is bound to the form using this

    Set me.recordset = rstTemp
    rstTemp.close
    set rstTemp = nothing


    the form behaves just as if it was bound to a local table...keeps me from havng to write the update code...besides...the form is in contnuous forms.

    Mike
    0
     
    LVL 83
    Have you tried changing the cursor:

    .CursorLocation = adUseClient

    0
     
    LVL 18

    Author Comment

    by:Data-Man
    yup...the recordset isn't updatable when I do that...Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    can we have a peek at the sproc that populates the form please?

    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Here it is.  The recordset returned used to be in another stored proc that returned 3 recordsets.  I thought that might be causing the problem so I put this one all by itself.  Same problem....Mike


    Alter      Proc pIMPLineItemListDetails
    (
          @strProject varchar(12) = 'MCHP'
          ,@strCategory char(4) = '0050'
          ,@strJobNumber char(11) = 'IMP S'
    )
    AS

    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER OFF

    --Get the list of line items for the selected project, section and category
    SELECT lp.*, l.[Description]
    FROM tblIMPLineItems2Projects lp INNER JOIN tblIMPLineItems l on lp.LineItemID = l.LineItemID
    WHERE lp.PCBJOBNUMBER = @strJobNumber AND lp.Project = @strProject and l.Phase = @strCategory

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Have you tried Me.Requery after the save?

    just playing around with northwind.adp at the moment, setting a new forms recordsource to a sproc

    Recordsource: exec CustOrdersDetail 10248

    I notice that if I set the  data mode to data entry, and open the form I dont even get any visible controls, hmmm. But yours has default values for the input params so should be ok

    Try this after the save, Me.Recordsource = "exec pIMPLineItemListDetails"

    Alan




    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Is this an ADP Mike?
    If so did you set the UniqueTable  property, you do this to make Views updateable with and ADP.

    Alan
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Another question Mike,

    after you do the save, what does Me.Dirty return???

    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    the database is an mdb file...not an adp.

    I just tried something and it seems to work.  I thought the problem might be in the where clause using the other table in the join.  I added the two fields from tblIMPLineItems (phase and description)  in order to remove the reference to the other table.  The SQL now looks like this.

    SELECT lp.*
    FROM tblIMPLineItems2Projects lp --INNER JOIN tblIMPLineItems l on lp.LineItemID = l.LineItemID
    WHERE lp.PCBJOBNUMBER = @strJobNumber AND lp.Project = @strProject and lp.Phase = @strCategory

    This seems to allow me to perform multiple updates...not done testing, but it seems to work.  I guess at some point I should move over to an ADP, but I'm so comfortable with the mdb and I've read that the ADP has some limitations when it comes to muti users.  

    Any ideas why ado doesn't like updating more than once when the where/select references fields from a joined table?
    I justed checked the version of ADO, and it was set to 2.1....I'll try 2.8 and see if the behavior is the same.

    Thanks,
    Mike
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Same error as before even when I moved to ADO.2.8

    I guess I'll have to keep things simple in the select statement when working with forms in 'continous forms view'

    Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    I'll see what I can do with an mdb connected to northwind Mike.

    Putting my ADP away...
    Do you have any linked tables or its all code?



    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    all code...no linked tables or queries....it's all ADO/stored procs....I create a global connection object on startup and use it for the entire instance of the application.  I'm also using trusted connections.

    Makes for a lot of code, but the application is built for speed right from the start.  No having to rework it after a year or two when the tables get some serious data in them.  We have a monster SQL server box and Terminal server box.  I'm actually in Phoenix and my client is in Philly.  I do all my development via terminal services (cable modem...the only way to go!!!!)

    Alan, you don't have to sped a lot of time on this, I think the answer is that MS hasn't quite worked out the form's recordset property/object.  When I bind an Access form to an ADO recordset, the recordcount property and recordsetclone property/object don't work like they do with a DAO recordset.

    Also, I can't put a text box in my form footer with a control source of "=Sum(Total)" like I can on a normal Access form, it just displays #Error

    Thanks again for your help,
    Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    I'm a little interested Mike,
    I usually use ADP's for sql catalogs, and control all access with Roles and User permissions in the server.
    Giving users exec permissions on some procs and mosify permissions on some views, but never any modify permission on any tables.

    hmmm, Star Trek just started, so maybe I take a little  break.

    Got the connection done, just instantiating the objRs and binding to the form, its a bugger you dont get the field list in design view.

    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Make sure you use a connection object.  I tried opening a recordset using the command object and the recordset wasn't updatable when bound to a form.

    I use this syntax when opening the recordset

    cn.pNameofStoredProc paramsList , rstTemp

    If Not rstTemp.EOF then
       Set Me.Recordset = rstTemp
    Else
       Me.Recordsource = ""
       'I also set the control source to an empty sting which removes the #Name? when there are no records to display...I set them again before I set the recordset to the form
    End If

    rstTemp.Close
    Set rstTemp = Nothing


    Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi Mike,

    How does it go if you include the Join key from tblIMPLineItems in the resultset?

    SELECT lp.*, l.[Description],l.[LineitemID]
    FROM tblIMPLineItems2Projects lp INNER JOIN tblIMPLineItems l on lp.LineItemID = l.LineItemID
    WHERE lp.PCBJOBNUMBER = @strJobNumber AND lp.Project = @strProject and l.Phase = @strCategory

    Alan
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi Mike,

    the only way I can set the forms recordset to the returned recordset is if I return a clientside cursor, which as you say is not updatable. If I try to set the forms recordset to the returned recordset that has a serverside cursor I get error 'the object you entered is not a valid recordset property'.

    Can you shed some light on how you are setting the forms recordset to a recordset object with a serverside cursor?

    strConnect = adoConnectSQL("pwd", "sa", "catalog", "domain")
    strProject = "MCHP"
    strCategory = "0050"
    strJobNumber = "IMP S"

    Set objCn = New ADODB.Connection
    With objCn
      .CursorLocation = adUseClient
      .ConnectionString = strConnect
      .Open
    End With

    Set objRs = New ADODB.Recordset
    With objRs
      .CursorLocation = adUseServer
      .CursorType = adOpenDynamic
      .LockType = adLockOptimistic
      .ActiveConnection = objCn
      objCn.pIMPLineItemListDetails strProject, strCategory, strJobNumber, objRs

      If Not .EOF Then
         Set Me.Recordset = objRs ' the object you entered is not a valid recordset property
      Else
         Me.RecordSource = ""
      End If
      .Close
    End With


    Set objRs = Nothing
    Set objCmd = Nothing
    Set objCn = Nothing


    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Alan,
      Thanks for taking a look at it.  Here is the code in my Open Event for a form...This one is completely updateable.  The next piece of code is the connection.  I found that when I included the ORDER By clause in the stored procedure the recordset because not updateable...I should write a book on mdb's and SQL Server.  Keep in mind that the provider for the connection must be "Microsoft.Access.OLEDB.10.0" in order for it to work properly.....Mike


    On Error GoTo errHandler

        Dim rstTemp As ADODB.Recordset
        Set rstTemp = New ADODB.Recordset

        DoCmd.Restore
        'DoCmd.RunCommand acCmdSizeToFitForm

        'Check the connection
        If cn.State = adStateClosed Then
            MTSBuildSmart_dbTrustedConnect
        End If
       
        With rstTemp
            .CursorLocation = adUseServer
            .LockType = adLockOptimistic
            .CursorType = adOpenDynamic
        End With
       
        'Get the data for the improvement combo box in the header of the form.  The stored SQL uses the PCB00175 table.
        'SELECT PCBJOBNUMBER, PCBJOBNAME, PCBJS FROM ' + @strDatabase + '.dbo.PCB00175 WHERE UPPER(LEFT(PCBJOBNUMBER,3)) = ''IMP'''
        cn.pIMPLineItemListAll rstTemp
        If rstTemp.EOF Then
            MsgBox "There are no line items setup.  Please contact your database administrator.", vbInformation + vbOKOnly, "No Line Items"
            Cancel = True
        Else
            rstTemp.MoveLast
            rstTemp.MoveFirst
            sngFormHeight = Me.Section(acHeader).Height + Me.Section(acFooter).Height + (IIf(rstTemp.RecordCount > 20, 20, rstTemp.RecordCount) + Abs(Me.AllowAdditions)) * (0.1875 * 1440)
            Set Me.Recordset = rstTemp
            Me.OrderBy = "Phase, Task"
            Me.OrderByOn = True
            Call Form_Resize
            DoCmd.RunCommand acCmdSizeToFitForm
        End If

    exitProc:
        DoCmd.Hourglass False
        Exit Sub

    errHandler:
        MsgBox (Err.Number & ", " & Err.Description)
        Call MTSBuildSmart_LogError(Me.FormName & "-Form_Open", Err.Number, Err.Description)
        Resume exitProc




    Function MTSBuildSmart_TestConnection(strTestServerName As String) As Boolean
    On Error GoTo errHandler

        Dim cnTemp As ADODB.Connection
        Set cnTemp = New ADODB.Connection
       
        Dim strCn As String
        Dim lngRecordsAffected
       
        'Test the new connection
        DoCmd.Hourglass True
       
        'Tell the user what we are doing
        Application.Echo True, "Testing connection to SQL server '" & strTestServerName & "'"
       
        'The uid of BuildSmart is a generic user associated with the database.  The user
        'has been setup with ready only access to the database.  I added this user so that I
        'could distinguish between an unknown user and a problem with finding the database
        strCn = "provider=Microsoft.Access.OLEDB.10.0;Data Provider=SQLOLEDB.1;Data Source=" & strTestServerName & ";uid=test;pwd=test;database=" & strDBNAME

        With cnTemp
            .ConnectionString = strCn
            .ConnectionTimeout = 10
            .Open
        End With

        'Tell them it worked
        Application.Echo True, "Connection to server '" & strTestServerName & "' enabled"
       
        'Close the connection
        cnTemp.Close
           
        DoCmd.Hourglass False
       
        MTSBuildSmart_TestConnection = True
       
    exitProc:
        Exit Function

    errHandler:
        MTSBuildSmart_TestConnection = False 'err number is -2147467259 Specified SQL server not found

    End Function
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    hmmm... Im using SQLOLEDB.1 provider

    Is this access XP OLEDB  """"Microsoft.Access.OLEDB.10.0"""""  ??

    Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)

      ' Returns SQLOLEDB ADO connect string
      ' Uses SQL Server security

      Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource

      sProvider = "Provider=SQLOLEDB.1;"
      sPassword = "Password=" & psPassword & ";"
      sPersist = "Persist Security Info=True;"
      sUser = "User ID=" & psUser & ";"
      sCatalog = "Initial Catalog=" & psCatalog & ";"
      sDataSource = "Data Source=" & psDataSource
      adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource

    End Function


    Alan
    0
     
    LVL 83
    What does this line do:

    cn.pIMPLineItemListAll rstTemp

    never seen it before as a property or method of an ADODB connection ... Googled it and couldn't find anything on it.
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    cn is my global conneciton object...

    pIMPLineItemListAll  is the name of the stored procedure.

    When used this way, the name of a stored procedure becomes a method of the connection object.  

    And rstTemp is the recordset populated by the data returned from the stored proc...any params would be listed like this...

    cn.pIMPLineItemListDetails Form_Switchboard.cboProject, Me.cboPhase, Me.cboImpSections, rstTemp


    Mike
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Alan,

       Yes, it is a provider specifically written for Access and SQL Server.  The data provider is SQLOLEDB.1

    Mike
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    My mdb has no tables or queries.  Only forms/reports and modules.

    Built for speed from the begining.

    Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi Scott,

    you can execute a stored proc/query by name directly off the connection object.

    cn.somequeryname rs

    If you stick an instantiated recordset object on the end, it will return the resultset as the recordset object.

    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Alan,

        To follow up with my comment yesterday about getting it to work...it does.  I had to remove the inner join to the other table and the order by clause...it works like a champ.  I am however only using the recordset object of the form to edit/delete the records, I have another set of text boxes in the header with an add button, behind that I wrote an insert SQL statement, then I just call the proc again to get the recordset.  Not as clean as I would have liked it, but it works like a champ.

    Mike
    0
     
    LVL 83
    Thanks guys ... didn't know that ... this ones going into the Library!!!
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    :-)

    Mike
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi Mike,

    I do a lot of this with ASP, so all web-forms are populated from dis-connected recordsets, the users can modify the data, then submit, I have another proc that expects the data sent back from the web as input params. Because I use disconnected recordsets, I tend to use a command object, to execute the updates, but I use the same method you use to retrieve the the disconnected recordset.

    What you are doing in parsing the the  recordset to the forms recordset with an active connection and a serverside cursor is new to me.


    take care...


    Alan
    0
     
    LVL 18

    Author Comment

    by:Data-Man
    Alan,

       Thanks for your thoughts.

    Mike
    0
     
    LVL 2

    Accepted Solution

    by:
    Closed, 250 points refunded.
    Lunchy
    Friendly Neighbourhood Community Support Admin
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now