clear out tables
-
to clean-up a table in xbase programming is as easy as typing the command "ZAP" then instantly all records gone! is there any possible way of doing this in recordsets or would i go to each and every row and issue the delete method? huh! i've got 10,000,000 records :-(
i guess i just have to create another rs object then delete * from TABLE. . .
thank you all! have a nice day. . . -
If you have 10M records, it's generally faster to
(assuming you have no constraints against other tables):
DROP table
then
Create table
Create index(es)
There is a discussion of this at:
Dev Ashish's Access Web - -
Alternatively, use
Truncate Table TheTableName
if you are using MS SQL Server, however, this will not save the data in the backup log -
well, i'm just using access table and i assume that it could store millions of records. can i use DROP or Truncate commands?
-
Hi!
Didn't have much time today but I just managed to fix this in a sec:
Create a form with a combobox named "boxTables" and a button named "btnClearTable".
Then add the next code to the form:
Option Compare Database
Private Sub Form_Activate()
[boxTables].RowSourceType = "Value List"
For Each Item In Application.CurrentDb.tabledefs
[boxTables].RowSource = [boxTables].RowSource & ";" & Item.Name
Next
End Sub
Private Sub btnClearTable_Click()
Dim strSQL As String
For Each Item In Application.CurrentDb.tabledefs
DoCmd.SetWarnings warningsoff
If Item.Name = [boxTables].Value Then
strSQL = "DELETE " & [boxTables].Value & ".* FROM " & [boxTables].Value & ";"
DoCmd.RunSQL strSQL
End If
DoCmd.SetWarnings warningson
Next
End Sub
Run the form and it will clear the table you select in the combobox. BE CAREFUL! You can make it safer by adding an "are you sure?" procedure or by leaving out the two lines with DoCmd.SetWarnings.
Have fun!
Thamar -
i receive an error when i use:
truncate table Employee
on my ClearEmployee_Click() event
how should i properly use this command to delete all rows in a table? (msaccess)
many thanks! -
Option Compare Database
Option Explicit
'------------------------------------------------------------
' DeleteAllRecords
'
'This function simply copys over an empty template
'of your database table over the one you wish to purge.
'Fastest way to do it if your having lot of records.
'you can remove the warning too by setting the echo event. <--- Was I tired or what??
'I ment the Warnings event LOL
'however it is not recomended
'------------------------------------------------------------
Function DeleteAllRecords()
On Error GoTo DeleteAllRecordsErr
DoCmd.CopyObject "", "YourTableName", acTable, "emptyTable"
Beep
MsgBox "All table records in YourTableName has been deleted.", vbInformation, "DELETE RECORDS"
DeleteAllRecordsExit:
Exit Function
DeleteAllRecordsErr:
MsgBox Error$
Resume DeleteAllRecordsExit
End Function -
i have an error on "DoCmd" part...
"variable not defined" :-( -
did you create a copy of your table first, blank, only format not contents.. to store as the TempTable which you copy over your table name?
-
hi there!
i'm actually creating an application in visual basic and i established a connection to a database created in ms access. what im trying to do is to clear out a table by creating a function in VB and not in MSACCESS environment. that is why i guess the "DoCmd" doesn't work. do you have any other idea on this?
i also have a program in vb this time it is connected to a visual foxpro databse thru odbc, and requires the same function.
thanks a lot for your time. . . -
This is easy...
SQL = "DELETE * FROM TableA"
Connection.Execute (SQL) -
I snagged a code source sample for something that may be of help to you. could even work over foxpro but I wouldnt know havnt done anything in it (yet)
this may give you some ideas to the same thing I described but in pure VB / ADO instead...
If you need the entire thing you can get it form http://www.planet-source-code.com search teh VB direcotries for
.. ADO MS Access it applies to a lot mroe though.. or write the author directly at the following info
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Database Tutorial v1.0
'Dustin Davis
'VB Live
'http://www.vblive.com
'
'This tutorial is to show you how to talk to databases with pure code
'no data object. This comes in handy for ASP applications or DHTML projects
'
'You will need a reference to DAO 3.6 (only if you use access 2000)
'and if you dont have it, get the service pack 3 from microsoft.com
'If you use access 98 or lower, you can use DAO 3.5
'
'Questions or comments, please send em to [email protected]
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Add_Table()
'This function will show how to delete records
'Dim our variables
Dim DB As Database
Dim WS As Workspace
Dim TD As TableDef
Dim FD1 As Field
Dim FD2 As Field
Dim FD3 As Field
'This sets a workspace for the database
Set WS = DBEngine.Workspaces(0)
'this opens the database
Set DB = WS.OpenDatabase(App.Path & "\dbtut.mdb")
'Set the table info
Set TD = DB.CreateTableDef(Text5.Text)
'create new fields and bind it to the table.
'For the dbText, you can use dbInteger or whatever else
'you wish to set the field type to. I would stick with those
'2 though.
Set FD1 = TD.CreateField(Text6.Text, dbText)
Set FD2 = TD.CreateField(Text7.Text, dbText)
Set FD3 = TD.CreateField(Text8.Text, dbText)
'bind the Fields to the table
TD.Fields.Append FD1
TD.Fields.Append FD2
TD.Fields.Append FD3
'Now bind the table to the database
DB.TableDefs.Append TD
'close the database
DB.Close
End Function
Public Function Delete_Table()
'This function will show how to delete records
'Dim our variables
Dim DB As Database
Dim WS As Workspace
Dim TD As TableDef
Dim FD As Field
'This sets a workspace for the database
Set WS = DBEngine.Workspaces(0)
'this opens the database
Set DB = WS.OpenDatabase(App.Path & "\dbtut.mdb")
'Set the table to open
DB.TableDefs.Delete Text5.Text
'close the database
DB.Close
End Function
Post a reply
Database forum discussion
-
Required Every quarter data from sql automatically in below sql format
by jain.piyush831 (0 replies)
-
Is Your Data is Lost? Learn Online How TO Recover Your Data.
by dataempires (0 replies)
-
Executing a command line SELECT
by cyoung311 (0 replies)
-
Softaken Mac OLM to PST Migration Software
by albertcolin012 (0 replies)
-
MAX value of each column - HELP
by triparipr (0 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).