Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    48

    Unanswered: 3622 error:, used dbOpenDynaset, dbSeeChanges. but no result

    I am getting 3622 error: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.


    I am using SQL Server 2000 database as backend, and using table with identitiy(autonumber) defined on it.

    when control reaches DB.execute, it gives error. I searched on web, they says to use dbseechanges etc. I sued, but not working yet.
    How to set dbOpenDynaset = 512 ??

    Private Sub Cmd_Change_Cost_Click()

    Dim DB As DAO.Database
    Set DB = CurrentDb
    Dim RS5 As DAO.Recordset

    Dim strSQL

    Me.Refresh

    strSQL = "Select MaterialId, BPCSPrice From Duplicate_Parts_Costing Where PriceChangeFlag <> 0 "

    Set RS5 = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    If RS5.RecordCount <= 0 Then
    MsgBox "There is no records selected for Price Change. Please Check box for records including for Price Change"
    Exit Sub
    Else

    RS5.MoveFirst

    'Loop for updating all records in tblmaterials table for New Price
    With RS5
    Do While Not .EOF

    DB.Execute "Update tblmaterials Set CurrentCostPerUnit = " & Nz(RS5(1), 0) & " " & _
    " Where MaterialId = " & RS5(0)

    .MoveNext
    Loop

    .Close
    End With
    End If

    End Sub

    thanks......

  2. #2
    Join Date
    Oct 2002
    Posts
    48

    Modification in above problem definition.

    Well, there is no problem with my recordset opening.

    I have table 'tblmaterials' on SQL Server. Which has Primary key 'MaterialId', which is autonumber (Identitiy on) field.

    I am using update statement, and updationg record of 'tblmaterials' using 'MaterialId' in where condition for poiting that particulr record.

    Eg. Update tblmaterials
    Set OldPrice = NewPrice
    Where MaterialId = <ParameterMaterial>

    So, error 3622 comes.

    If, I use following update statement, then there is no problem.
    Because, 'OldPrice' is not primary key as well as no Identity column(autonumber).

    Eg. Update tblmaterials
    Set OldPrice = NewPrice
    Where Oldprice = <ParameterPrice>

    Erro, I am getting while using DB.Execute.

    If I uses DoCmd.RunSQL "<SQL statement>", there is no problem.
    But, At each update statement(It is loop), it confirms, and show message. '!you are about to Update 1 Row(s)' . I want to avoid this message, is there anyway, I can avoid this message to be shown everytime, when update executes.

    Thanks,

  3. #3
    Join Date
    Oct 2002
    Posts
    48

    Found Solution.

    Guys,

    solution is there, in case of updation of table, which has IDENTITY field, and that field is used in Where condition for updating that table's other fields. It was giving me error.

    Instead of using DB.Execute. I used DOCMD.RUNSQL "< staement> ".

    But tiwas asking ofr confirmation each time. And statement was in loop, so, every update, it was asking me. so, Its warking was set off. So, it is working now.

    Please see below code for more info.

    Private Sub Cmd_Change_Cost_Click()

    Dim DB As DAO.Database
    Set DB = CurrentDb
    Dim RS As DAO.Recordset
    Dim TempMaterialId As Integer

    Dim strSQL

    Me.Refresh

    strSQL = " Select MaterialId, BPCSPrice, CurrentCostPerUnit From Duplicate_Parts_Costing " & _
    " Where PriceChangeFlag <> 0 "

    Set RS = DB.OpenRecordset(strSQL)

    If RS.RecordCount <= 0 Then
    MsgBox "There is no records selected for Price Change. " & _
    " Please Check box for records including for Price Change"
    Exit Sub
    Else

    'Setting warnings off, for confirming for each update statement in below loop
    DoCmd.SetWarnings False

    'Loop for updating all records in tblmaterials table for New Price
    With RS
    Do While Not .EOF

    DoCmd.RunSQL "Update tblmaterials Set CurrentCostPerUnit = " & Nz(RS(1), 0) & " Where MaterialId = " & RS(0), 0

    .MoveNext
    Loop

    .Close
    End With

    'Setting warnings on
    DoCmd.SetWarnings True
    End If

    End Sub

    --

    Thanks for viewing this query.

Posting Permissions

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