Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO Write Conflict - Bound Access Form

Posted on 2004-10-25
31
Medium Priority
?
1,307 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
Comment
Question by:Data-Man
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
  • 3
  • +2
31 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12405054
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
ID: 12405334
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 85
ID: 12405335
Have you tried changing the cursor:

.CursorLocation = adUseClient

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Author Comment

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

Expert Comment

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

Alan
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12410533
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
ID: 12410672
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
ID: 12410713
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
ID: 12410741
Another question Mike,

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

Alan
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12410792
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
ID: 12410847
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
ID: 12410993
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
ID: 12411122
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
ID: 12411326
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
ID: 12411453
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
ID: 12420234
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
ID: 12421300
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
ID: 12421488
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
ID: 12421617
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 85
ID: 12421681
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
ID: 12421762
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
ID: 12421778
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
ID: 12421788
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
ID: 12421789
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
ID: 12421853
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 85
ID: 12421874
Thanks guys ... didn't know that ... this ones going into the Library!!!
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12422002
:-)

Mike
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12422071
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
ID: 12422225
Alan,

   Thanks for your thoughts.

Mike
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 12478826
Closed, 250 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 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