Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: MS Access frontend - displays '#Deleted'

    I'm using Access XP as a frontend to a MySQL database. I have the latest updates to the Microsoft Jet DB engine installed and I'm running MySQL 4.0.13. Is there a way to avoid Access displaying "#Deleted" when a new record is being added to a table? The data is saved properly and appears correctly when the table is closed and reopened. This seems to occur when the Primary Key is set as auto_increment. I'd appreciate any help you can offer.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: MS Access frontend - displays '#Deleted'

    i still got exactly the same problem even i ve applied a lot of advises from a lot of forums. frustrated i finally do this and very happy with the result.

    in before_insert event of the form, i write vb codes to query the maximum number in the table, add 1 to it and set it to the auto_increament field. something likes:

    Function MaxEID()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim tmpEID As Long

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.ActiveConnection = cn
    rs.Open "qMaxEID", cn, , , adCmdTable
    tmpEID = rs!MaxEID
    MaxEID = tmpEID
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    End Function

    where "qMaxEID" is a pre-designed query to get the maximum value from auto_increament field and the following codes will "auto" add the new number when you insert new record.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    'Add new event ID
    Me.EventID = MaxEID() + 1
    End Sub

    hope this helps
    Last edited by qha_vn; 07-09-03 at 05:31.
    qha_vn

  3. #3
    Join Date
    Jul 2003
    Posts
    8

    Smile compact database

    hey All,

    U can actually compact the Access database.
    For wat i know, when u delete the data inside the database, the Access actually have a cache inside. U can notice that the file size of Access DB will keep on increasing.
    So, u can compact the database so that it will empty the cache.



    Cheers,
    LOOI

  4. #4
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Hi,

    i dont think this can solve the above mentioned problem.

    cheers
    qha_vn

  5. #5
    Join Date
    Jul 2003
    Posts
    4
    FYI - this behavior stopped once I added a timestamp field to each table. Thanks for everyone's replies.

  6. #6
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by jeffmun
    FYI - this behavior stopped once I added a timestamp field to each table. Thanks for everyone's replies.
    good to hear that, but i do have timestamp field in every single table as advised by mysql support (use access as front end) its still happen. anyway, hope you wont.

    cheers,
    qha_vn

Posting Permissions

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