Hi all,

This is quite an urgent post. I'm building a website for a photo company, and all going to plan except for one thing...the access database keeps corrupting!! This happens every now and then, but usually when a record is deleted (using an ASP page).

Basically the only part of code that messes it up is when the .LockType is set to Optimisic!  But I need this to do all the operations right? Add, Update, Delete etc?

Here's the error I recieve:

Microsoft JET Database Engine error '80004005'

Invalid operation.

/gallery/search.asp, line 78

I've searched the Internet, for somebody else with the problem, no body else has it, its got to be bad right?

When just one record is deleted from one table, 3 tables become corrupt. For example I delete a photo, then my configuration table is duff, photo table is duff and guestbook table.

Honestly, all that goes on when these records are deleted is a simple delete function in asp or sql executed:

With rsPhotoDetail

bla bla bla code
' Delete record
.Delete
End With

OR

strSQL = "DELETE bla bla code"
objConn.Execute(strSQL)

If I do a compact and repair in Access a couple of times the error goes away. I think you will all appreciate, that no way caan I do that every time the database gets corrupt.

NOTE: Please do not spend the time writing a reply if you are going to advise me to use a compact and repair tool online, been there, done that, bought the t-shirt. All that does is make it worse

Please, can somebody help me

The 80004005 error indicates you either don't have permission to access an object, or the object doesn't exist. An optimisitc lock type only locks the record during update - pessimistic locks it during edit as well.

I'd suspect that there's a table relationship (e.g. key field/foreign key, inner/outer join, cascading deletes, etc.) that is not being taken care of by the update. Possible reasons could be because it's changing between retrieval and update, because a object of the relationship doesn't exist or is null, or you're deleting a record referenced by an index field in another table without updating the other table.

Post the table schema and the sql you're using.

I suspect you mean the sql that deletes the record, right?

' If they've used the form then run the command
If (cStr(Request("Delete")) <> "" AND cStr(Request("chkDeleteGBEntry")) <> "") Then
 
 ' If the request were made do the replacing
 If (Replace(Request("chkDeleteGBEntry"),", ","OR EntryId = ") <> "") Then cmdDeleteCheck__Delete_IDs = Replace(Request("chkDeleteGBEntry"),", ","OR EntryId = ")
 
 ' Run the ADO Command
 Set cmdDeleteCheck = Server.CreateObject("ADODB.Command")
 cmdDeleteCheck.ActiveConnection = MM_ole_STRING
 cmdDeleteCheck.CommandText = "DELETE FROM tblGBEntries WHERE EntryId = " + Replace(cmdDeleteCheck__Delete_IDs, "'", "''") + ""
 cmdDeleteCheck.CommandType = 1
 cmdDeleteCheck.CommandTimeout = 0
 cmdDeleteCheck.Prepared = true
 cmdDeleteCheck.Execute()
 
 ' Re-Direct to the manage page
 Response.Redirect("admin_gb_manage.asp")
End If

Whats wrong here?

aahhh i hate that type of ASP

i perfer the easy read (as i call it)...

anyway sorry i can't be to much help with the above but im off to bed in a min and not thinking 100%

but i was going to give you a pointer unless you have to its best not to just have request("value") .. because it can leave you open to attacks best to add the .form , .querystring and  .cookies ... so for example

request.form("Value") .. means it can only be sent in the form .. if that wasn't there it could easily be sent via querystring.

Sorry, about that. I've been under a lot of pressure to complete this project fast, so I've had to resort to using Dreamweaver MX for some of the ASP.

Most of code usually is quite clean, looking at it now, it does look like quite a bodge .

Anywayz, I read through your first reply again, and a light bulb came on! I realised that there quite a lot of indexing in my Access database in certain fields that don't need to be indexed. So I'm now going to remove them and also change the relationships.

I'll keep you updated with any problems. Thanks for your help and advice, I appreciate it