MS Access frontend - displays '#Deleted'

jeffmun · #1 (**permalink**) 07-08-03, 12:24

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.

qha_vn · #2 (**permalink**) 07-09-03, 05:28

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

LOOI YS · #3 (**permalink**) 07-09-03, 07:17

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

qha_vn · #4 (**permalink**) 07-10-03, 04:03

Hi,

i dont think this can solve the above mentioned problem.

cheers

jeffmun · #5 (**permalink**) 07-10-03, 11:27

FYI - this behavior stopped once I added a timestamp field to each table. Thanks for everyone's replies.

qha_vn · #6 (**permalink**) 07-10-03, 23:04

Quote:

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,