Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: Updating and locking tables with VBA and ADO?

    i have 3 tables:
    Code:
    ------------------------------------------
    | tbl1                                            |
    ------------------------------------------
    |  id (Primary Key) | text (varchar) |
    ------------------------------------------
    
    ------------------------------------------
    | tbl2                                            |
    ------------------------------------------
    |  id (Primary Key) | text (varchar) |
    ------------------------------------------
    
    -----------------------------------------------------
    | tbl3                                                          |
    -----------------------------------------------------
    |  tbl1id (Primary Key) | tbl2id (Primary Key) |
    -----------------------------------------------------
    
    (ids in tbl1 and tbl2 are create automatic by database)
    (ids in tbl3 are foreign keys to tbl1 and tbl2)
    There are more than one user using the tables at the same time, but i wan't to lock the table while i want to update it.
    So the tables need to be locked readonly.
    Now the new value for tbl1 is checked, whether it is already present, if not it is added. Next the id in tbl1 for the new value is read and to be saved in a variable temporarely.
    The same happens to tbl2, new value added if not already present and read of the id.
    Finally both ids are checked, whether they are present in this combination in tbl3. If not the new combination is added to tbl3.
    During the process other users shall be able to read from the database.
    Can you tell me how to write this using ADO in Access 2000?

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    You need an sample of code for write in ADO or what...??
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Posts
    24
    I've found out, that stadart Locktype is ReadOnly, so that i just need to create 3 recordsets, one for every table and use AddNew to append futrher data to the tables.
    Question: Am i able to start a query using the open recordobject searching for the id or do i have to create further recordsets uning the Execute Method of the Connection Object?

    thanks

    //Edit
    A sample code for the problem may help to (i hope so)

    Am i able to use a Transaction with the procedure to be sure that the changes are rolled back if an error occured in one of the tables?
    How do I use the BeginTrans, CommitTrans and RolbackTrans? In Java rolled the Transaction back if an Exception occured. Is it enough to use on error?

    thanks
    Last edited by dragun; 12-23-03 at 09:30.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by dragun
    I've found out, that stadart Locktype is ReadOnly, so that i just need to create 3 recordsets, one for every table and use AddNew to append futrher data to the tables.
    Question: Am i able to start a query using the open recordobject searching for the id or do i have to create further recordsets uning the Execute Method of the Connection Object?

    thanks

    //Edit
    A sample code for the problem may help to (i hope so)

    Am i able to use a Transaction with the procedure to be sure that the changes are rolled back if an error occured in one of the tables?
    How do I use the BeginTrans, CommitTrans and RolbackTrans? In Java rolled the Transaction back if an Exception occured. Is it enough to use on error?

    thanks
    I send an sample of Code is in spanish but is a code.....:

    Function ActuaCtaCteEdi()
    On Error GoTo ControladorErr
    Dim strCodigo As Long, strTipo As String, strFecha, strFecha1
    Dim dbs As Database
    Dim rst1 As Recordset, rst As Recordset, rst2 As Recordset
    Dim strCriteria As String
    Dim wrkPredeterminado As Workspace
    Dim TotalPago As Variant
    Dim NCOMOV As Long
    Dim strNumero As Variant


    ' Devolver referencia a la base de datos actual.
    Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\GralRoca\A genbase.mdb")
    Set wrkPredeterminado = DBEngine.Workspaces(0)
    ' Crear el objeto Recordset de tipo tabla.
    Set rst = dbs.OpenRecordset("Tbl Nros Comprobantes", dbOpenTable)
    Set rst1 = dbs.OpenRecordset("Tbl ED Movimientos", dbOpenDynaset)
    Set rst2 = dbs.OpenRecordset("Tbl ED Publicaciones", dbOpenDynaset)

    rst.Index = "PrimaryKey"


    wrkPredeterminado.BeginTrans

    strCodigo = 1
    rst.Seek "=", strCodigo

    ' Comprueba si se ha encontrado un registro.
    If rst.NoMatch Then
    MsgBox "No Encuentra Registro Tbl Nros Comprobantes", 16
    GoTo Terminar
    Else
    NCOMOV = rst!PAUEDI + 1
    End If


    ' Actualiza Tbl ED Movimientos con Pagos a Editoriales

    strFecha1 = Format(Me![Desde], "mm/dd/yyyy")
    TotalPago = Forms![LiquidacionEditoriales]![Valor]
    strCodigo = Forms![LiquidacionEditoriales]![Editorial]
    strFecha = Format(Me![Hasta], "mm/dd/yyyy")
    strTipo = "PA"

    If TotalPago = 0 Then
    GoTo Terminar
    End If

    strCriteria = " [CODEDI] = " & strCodigo & " and [HASLPE] = #" & strFecha & "# and [CODCOM] = '" & strTipo & "'"
    ' Busca el primer registro que cumpla el criterio.
    rst1.FindFirst strCriteria

    If rst1.NoMatch Then
    GoTo Alta
    Else
    GoTo Modificar
    End If

    Alta:
    rst1.LockEdits = False
    On Error GoTo ControladorErr
    With rst1
    .AddNew
    !CODCOM = "PA"
    !CODEDI = strCodigo
    !TIPOMO = " "
    !NCOMOV = 0
    !FORMUL = " "
    !NUMCOM = NCOMOV
    !FECMOV = Date
    If Forms![LiquidacionEditoriales]![Valor] = 0 Then
    !REFMOV = "No se Ingreso Importe en esta Liquidación"
    Else
    !REFMOV = LTrim(Forms![LiquidacionEditoriales]![Adjunta]) & " " & LTrim(Forms![LiquidacionEditoriales]![Banco]) & " " & "Nº" & LTrim(Forms![LiquidacionEditoriales]![Nro]) & " Período " & LTrim(Forms![LiquidacionEditoriales]![Desde]) & " al " & LTrim(Forms![LiquidacionEditoriales]![Hasta])
    End If
    !TOTMOV = Forms![LiquidacionEditoriales]![Valor]
    !HASLPE = Format(Me![Hasta], "dd/mm/yy")
    .Update
    .Bookmark = .LastModified
    .MovePrevious

    End With

    rst.LockEdits = False
    On Error GoTo ControladorErr
    With rst
    .Edit

    !PAUEDI = NCOMOV

    .Update
    End With

    GoTo CtaPubli

    Modificar:

    With rst1
    .Edit
    !TOTMOV = TotalPago
    .Update
    .Bookmark = .LastModified
    .MovePrevious

    End With

    GoTo Terminar
    '************************************************* **********
    CtaPubli:

    strNumero = Format(Val("0000" & Format(NCOMOV, "00000000")), "000000000000")

    strCriteria = " [CODEDI] = " & strCodigo & " and [NUMCOM] = " & strNumero & " and [CODCOM] = '" & strTipo & "'"

    rst2.FindFirst strCriteria

    If rst2.NoMatch Then
    GoTo AltaCtaPubli
    Else
    GoTo ModificarCtaPubli
    End If

    AltaCtaPubli:
    rst2.LockEdits = False
    On Error GoTo ControladorErr
    With rst2
    .AddNew
    !CODEDI = strCodigo
    !CODCOM = strTipo
    !FORMUL = " "
    !NUMCOM = strNumero
    !FECMOV = Date
    If Forms![LiquidacionEditoriales]![Valor] = 0 Then
    !REFMOV = "No se Ingreso Importe en esta Liquidación"
    Else
    !REFMOV = LTrim(Forms![LiquidacionEditoriales]![Adjunta]) & " " & LTrim(Forms![LiquidacionEditoriales]![Banco]) & " " & "Nº" & LTrim(Forms![LiquidacionEditoriales]![Nro]) & " Período " & LTrim(Forms![LiquidacionEditoriales]![Desde]) & " al " & LTrim(Forms![LiquidacionEditoriales]![Hasta])
    End If
    !TOTMOV = Forms![LiquidacionEditoriales]![Valor]


    .Update
    .Bookmark = .LastModified
    .MovePrevious

    End With

    GoTo Terminar

    ModificarCtaPubli:

    With rst2
    .Edit
    !TOTMOV = TotalPago
    .Update
    .Bookmark = .LastModified
    .MovePrevious

    End With

    Terminar:

    wrkPredeterminado.CommitTrans

    Cerrar:
    rst1.Close
    rst.Close
    rst2.Close
    Set dbs = Nothing

    Exit Function

    ControladorErr:
    MsgBox "Número de error " & Err.Number & ": " & Err.Description
    Dim Mensaje, Estilo, Título, respuesta, MiCadena


    Mensaje = "¿Desea Reintentar la Operacion?" ' Define el mensaje.
    Estilo = vbYesNo + vbCritical + vbDefaultButton2 ' Define los botones.
    Título = "Recuperación de Errores" ' Define el título.


    respuesta = MsgBox(Mensaje, Estilo, Título)

    If respuesta = vbYes Then ' El usuario eligió el botón Sí.
    MiCadena = "Sí" ' Ejecuta una acción.
    Resume
    Else ' El usuario eligió el botón No.
    wrkPredeterminado.Rollback ' Ejecuta una acción.
    GoTo Cerrar
    End If



    Fin:

    End Function
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Posts
    24
    puh, spanish is not my language
    But it helped me alot, i've worked it aout and tested it successfully with ADO.

    bye

Posting Permissions

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