URGENT: ASP with Access Error

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

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?

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