Cache Table Deletion 1

[jameshall]

Hello
I have a database that when it opens it populates a "cache" table with 'Sensitie Information'
The problem is when the database is closed (using a quit button) it is necessary to delete the contents of the table. The table is stored locally but the database is split.
I wrote a procedure that deletes the contents of the table but when I go to open the database again it crashes!

The code for this routine is...

Public Function DelTable()
Dim rs1 As Recordset

Set rs1 = CurrentDb.OpenRecordset("cache&quot
If Not rs1.EOF And Not rs1.BOF Then

rs1.MoveFirst
Do While Not rs1.EOF
rs1.Delete
rs1.MoveNext
Loop
Else
MsgBox "You have already deleted the table", vbInformation
End If
rs1.Close
Set rs1 = Nothing
End Function




Does anyone know of a better way of doing this?

Thanks in advance

jameshall

 

[GHolden]

This will be much quicker if you want to delete all the records

Dim db As Database
Dim strSQL As String

strSQL = "DELETE FROM cache"
Set db = CurrentDb

db.Execute strSQL

db.Close
Set db = Nothing There are two ways to write error-free programs; only the third one works.

 

[jameshall]

Thanks GHolden

The whole db works quicker

Thanks again

jameshall

 

[danvlas]

First...the crashes:
Open any module
Go to Debug menu
Choose Compile and Save all modules
Compact the database.

Secondly, why don't you use a Delete query?

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From cache;"
DoCmd.SetWarnings True

And finally, you could simplify the code a little bit (why displaying any message, since it has no effect?)

Public Function DelTable()
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("cache&quot
On Error Resume Next
With rs1
While Not .EOF
.Delete
.MoveNext
Wend
.Close
End With
Set rs1 = Nothing
End Function

It seems to be more suitable as a Sub, not Function...Is there a particular reason to create it as a Function?

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[danvlas]

Wow I was really slow on this... Two posts already...I must be getting rusty...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[GHolden]

Never mind Dan, your response was much more thorough.



There are two ways to write error-free programs; only the third one works.

 

[BVerlaan]

Deleting the complete content of a table could be done with the following.

Public Function DelTable()
Dim StrSQL As String
StrSQL = "DELETE [Cache].* FROM [Cache];"
'Remove all of the records in table Cache without warning
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL, True 'True here means: use Transaction
DoCmd.SetWarnings True
End Function

Perhaps someone else knows why your db crashes every next time you open it (with the present code).

General advise: make explicit DAO recordsets or ADO recordsets.
Dim rs1 As Recordset
would become
Dim rs1 As DAO.Recordset

Good luck, Bart Verlaan.

 

[oharab]

If you are adding and deleting records, your db must be getting huge!
Wi might be better to use a temporary mdb file instead of a table in your file.

Create an new password protected mdb on your c drive call it something like "C:\~tmpDat.dat". Put the table with sensitive data in it (you don't need to put any data in at this point).
In your front end file create a link to the table in this file.

Now delete the temp file you just created.


Now you can use this function to create the temp file each time the user logs on. As it is on his C drive it won't affect anyone else & will keep your front end file nice and slim. If you let me know some information of what the table with sensitive data in looks like & how you populate it, I will amend the code to do all that for you.

Ben

Function CreateMDB()
Dim thisDB as DAO.Database

Set tmpDB=CreateDatabase("C:\~tmpDat.dat",dblanggeneral)

'put code here to build table & populate it:

tmpdb.newpassword("","NewPass&quot
tmpdb.close
set tmpdb=nothing

End function

----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------