How to clear all Records from Table

[Maniac777]

Hi!

I've problems with clearing the table's records. I've alredy tried some of the cummon way but instead of deleting it does nothing, neighter shows the debugger an error-msg.

I'm using the DAO module. I'ms sure there are better modules but I need this module...

The code I use:
------------------------------------------
Public Function clearRecords()

Set db = DBEngine.OpenDatabase(db_name)
db.Execute "DELETE * FROM paData"

End Function
------------------------------------------

The is no password set...
Could you show me another way to handle this problem?
Do you know a quite good manual for DAO beside msdn?

tia

PS: Sorry for my poor english

 

[HarleyQuinn]

Hi Maniac777,

I modified the code you supplied slightly for my ease of use and it works fine for me:

Private Sub Command1_Click()
clearRecords ("c:\DB1.mdb")
End Sub

Public Sub clearRecords(db_name As String)
Set db = DBEngine.OpenDatabase(db_name)
db.Execute "DELETE * FROM tbl_access_test"
Set db = Nothing
End Sub

The only time it didn't appear to work was when the DB was open. I closed it and the records had deleted as expected.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[RoyVidar]

Just wondering, would

[tt]db.Execute "DELETE * FROM paData", dbFailOnError
debug.print db.recordsaffected[/tt]

give you some more information?

Roy-Vidar

 

[Maniac777]

thx for answering that rapidly.

It still doesn't work

The code that RoyVidar gave me shows only a 0 and Records().

I'm at one's wits' end...

 

[petermeachem]

That is how to delete records. If it doesn't there is something else up. Does Access still show the data?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gmmastros]

Perhaps you could try

"Truncate Table paData"

Understand full well that Truncate Table will ALWAYS delete all rows from the table. When you use the delete syntax, you can choose which fields are deleted. Ex. Delete * From People Where Eyecolor = 'Blue'



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[petermeachem]

Don't think Truncate works on Access does it. Anyway, Delete is fine, the problem is somewhere else.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gmmastros]

Peter, you're right of course. I haven't seriously used access in several years, but I could have sworn that Truncate worked. Maybe it's old age creeping up on me.

I do apologize if I've caused any confusion.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[waytech2003]

Have you tried to compact or repair the Database? Problem might be a corruption issue.

 

[Maniac777]

@petermeachen
The records are still there and the file still has the same size...

@waytech2003
No I haven't tried yet, because I'm not sure how it works...

 

[shakespeare5677]

Do you have any other connections/recordsets opened of the same table? If so, then they could be keeping the records from updating properly. I always work with ADOs, but I'm pretty sure DAO is very similar, so once you've made sure nothing else is using the table at the same time, try closing the connection (if that's possible) as someone has already suggested. Good luck.
-Max

 

[HarleyQuinn]

Create a new DB with a test table, stick some data in quickly and then try to delete from that table. If it work's you will know that it is a problem with the DB you are using rather than (as we already know) a problem with the code.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[DrJavaJoe]

Shouldn't this
"DELETE * FROM paData"
be
"DELETE FROM paData",



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[shakespeare5677]

I think "DELETE * FROM TableName" = "DELETE FROM TableName", but I've always used the first one.
-Max

 

[petermeachem]

DELETE * FROM paData"
be
"DELETE FROM paData",

Either ok for Access, the first is bad for SQL

 

[DrJavaJoe]

I haven't used Access for a long while now and have always just used the latter.


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[petermeachem]

Maniac777 , the file will stay the same size or possibly get bigger if you delete records.

 

[bjd4jc]

Is it possible that there are relations in this table that are not allowing you to delete anything from it? I would try running your query from within Access itself and see what happens. I imagine that there is an outside chance that you are using On Error Resume next in your code somewhere and this is why you don't see any error in the code. Also, if you right click in the VB code windw and do Toggle... Break On All Errors and then run it, see if you get a debug window then...

Also, the compact and repair is a good thing to run on your database on a schedule. Access never reclaims any space and uses a lot of temporary objects that do not ever get reclaimed either. When the database is bloated it starts having weird errors that are fixed upon a compact/repair operation.

 

[Maniac777]

Thx for your help. Now it works...

But I've another dao related problem. I try to execute this code and I receive an Errormsg:
------------------------------------
Runtime Error '3075'
Syntax Error(missing operator) in query expression
'Project=" & prno & " AND EmployeeSupplier=[" & emplsupp & "] SORT BY [Item_Date] ASC"'
------------------------------------


Code
#####################################
Public Function generatePSR() 'ByVal prno As String, ByVal emplsupp As String)

Dim temp(3), prno, emplsupp, i, j As Variant
Set db = DBEngine.OpenDatabase(db_name)
prno = "47110815"
emplsupp = "World, Mr. Hello"
query = "SELECT Billable, [Item_Date], Quantity, UOM FROM paData WHERE Project=" & prno & " AND EmployeeSupplier=[" & emplsupp & "] SORT BY [Item_Date] ASC"
Set rst = db.OpenRecordset(query)
j = 0

Do Until rst.EOF
i = 0
For Each fld In rst.Fields
temp(i) = fld
i = i + 1
Next
generatePSR(j) = Array()
Debug.Print temp(0) & temp(1) & temp(2) & temp(3)
j = j + 1
Loop

End Function
########################################

What does this msg tell me? And how can I handle this Problem?

 

[HarleyQuinn]

Not sure why you've ['ed off some fields and not others but, it might work if you try:

WHERE Project=[red]'[/red]" & prno & "[red]'[/red] AND EmployeeSupplier=[red]'[/red]" & emplsupp & "[red]'[/red]...

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.