Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy a record to a table in a different database' (Access 2K)

    I have a frmClients that is based on tblClients. Whenever a client is deleted from this form/table I would like for the record to be copied to a table in a different database. Despite prompting my users if they are sure they want to delete a client my users still do accidental deletions. I want to be sure a client's record is still around in case my user did an acidental deletion.

    I have listed below the code that I am using to copy the deleted record to a temporary table. However this code only work if the temporary table(tblDeletedClients) reside on the same database. I would like to change the code so that I can store the tblDeletedClients on a different database separated from the one my clients are using. How can I change the code to achieve this kind of set-up? Thank you.

    Const conBackEndPath = "GmoreClientsData"

    Private Sub Form_Delete(Cancel As Integer)

    Dim wrk As Workspace
    Dim DB As Database
    Dim ctlSSN As Control
    Dim qdf1 As QueryDef

    On Error GoTo Form_DeleteErr
    Set DB = DBEngine.Workspaces(0).OpenDatabase(conBackEndPath & "Clients_be.mdb")
    Set wrk = DBEngine.Workspaces(0)
    Set ctlClientSSN = Me!txtClientSSN

    Set qdf1 = DB.CreateQueryDef("", "INSERT INTO tblDeletedClients " & _
    "SELECT * " & _
    "FROM tblClients " & _
    "WHERE ClientSSN = '" & ctlClientSSN & "';")

    DoCmd.SetWarnings False
    qdf1.Execute
    DoCmd.SetWarnings True

    Form_DeleteDone:
    Exit Sub

    Form_DeleteErr:
    Select Case Err.Number
    Case Else
    MsgBox Err.Number & " - " & Err.Description
    End Select
    Resume Form_DeleteDone

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy a record to a table in a different database' (Access 2K)

    What I usually do in such a situation is to add a Yes/No field Deleted to the table. The form is based on a query that selects all records for which Deleted is No/False.
    The form doesn't allow users to delete a record (the AllowDeletions property has been set to No), but it contains a command button with caption Delete Record that just sets the Deleted field to Yes/True.
    So if someone accidentally "deletes" a record, retrieving it is as simple as setting the Deleted field to No.

    In your situation, you can link the backend table into the frontend database. That way, you don't have to worry about the fact that the data are actually stored in another database. You can run queries etc. just as if the data were present in the frontend.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a record to a table in a different database' (Access 2K)

    I realize there's a more complex way to do this (Hans will post it I'm sure), but for simplicity's sake, could you not just link the table?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy a record to a table in a different database' (Access 2K)

    Great minds... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a record to a table in a different database' (Access 2K)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    It's that student-teacher thing, where the student will develop the same habits as the teacher...
    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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