By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Delete Few Table's Record in One Query

P: 11
Anyone know how to create a SQL or way in Access for deleting few tables' record. I means is delete all the records in specify few tables.

I tried to use query to delete the records' table but only one table by one query only.

Anyone know the better way?



shinyo
Feb 13 '07 #1
Share this Question
Share on Google+
18 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Anyone know how to create a SQL or way in Access for deleting few tables' record. I means is delete all the records in specify few tables.

I tried to use query to delete the records' table but only one table by one query only.

Anyone know the better way?



shinyo
This depends on how you are deciding which tables you want to delete the data from.

Is it all tables or just certain tables?
Is it the same tables each time?

This can be done in code but how will depend on the answer to these questions.

Mary
Feb 13 '07 #2

P: 11
This depends on how you are deciding which tables you want to delete the data from.

Is it all tables or just certain tables?
Is it the same tables each time?

This can be done in code but how will depend on the answer to these questions.

Mary
example i have 10 tables in a database. I want to delete 7 tables of them but just want to delete only the content of the 7 tables but not the tables it self. Cox I still need the table just want it to be empty.

Thank u
Feb 13 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
example i have 10 tables in a database. I want to delete 7 tables of them but just want to delete only the content of the 7 tables but not the tables it self. Cox I still need the table just want it to be empty.

Thank u
Is this something you want to do on a regular basis and will it always be the same 7 tables.

Mary
Feb 13 '07 #4

P: 11
Is this something you want to do on a regular basis and will it always be the same 7 tables.

Mary
Its regular basic, the same tables will be delete. So is there any query could help ?
Feb 13 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
The SQL for emptying a table (A very good idea IMHO - I use that concept all the time) is very simple.
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM TableName;
In Access you can't run more than one per SQL execution though. You would have to do a list of them in VBA code.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "DELETE * FROM "
  4. DoCmd.RunSQL strSQL & "Table1"
  5. DoCmd.RunSQL strSQL & "Table2"
  6. DoCmd.RunSQL strSQL & "Table3"
Do you get the idea?
Feb 13 '07 #6

P: 11
The SQL for emptying a table (A very good idea IMHO - I use that concept all the time) is very simple.
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM TableName;
In Access you can't run more than one per SQL execution though. You would have to do a list of them in VBA code.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "DELETE * FROM "
  4. DoCmd.RunSQL strSQL & "Table1"
  5. DoCmd.RunSQL strSQL & "Table2"
  6. DoCmd.RunSQL strSQL & "Table3"
Do you get the idea?

Hi Neo, I don't understand what do u means VBA code? sorry I am newbie in database enviroment.

Do I need to install any other new software (SQL)?
Feb 14 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
VBA = Visual Basic for Applications and is the Programming Language built into Access.
The Modules tab can hold these types of items and so can Forms and Reports.
To go into the editor just hit Alt-F11 from Access and have a look around.
This is a fundamental feature of Access and requires no extra software to be installed.
Feb 14 '07 #8

P: 11
I had made a form in the Access. I add a command for the event of click(). After that, am I need to move that code you gave me ?
Example :

Expand|Select|Wrap|Line Numbers
  1. Private Sub  cmdDelRec_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. strSQL = "DELETE * FROM "
  6. DoCmd.RunSQL strSQL & "Table1"
  7. DoCmd.RunSQL strSQL & "Table2"
  8. DoCmd.RunSQL strSQL & "Table3"
  9.  
  10. End Sub
Feb 14 '07 #9

P: 11
I have one more question. After I deleted all the tables' content, what is the code I could use to make sure all the auto index number reset it back to zero? Existing I use the way "Compact and Repair Database" manually.

Anyway use SQL or VBA ?
Feb 14 '07 #10

P: 11
Hi Neo, I have added the code into the VBA, its seem every tables the sql run will prompt for "YES/NO". I had 20 over tables and it prompt for 20 overs times. Can I remore the prompt for this QUERY?

Thank
Feb 14 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
I have one more question. After I deleted all the tables' content, what is the code I could use to make sure all the auto index number reset it back to zero? Existing I use the way "Compact and Repair Database" manually.

Anyway use SQL or VBA ?
One Q at a time ;)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Exist returns true if strFile exists.
  5. '22/05/2003 Rewritten with better code.
  6. '20/05/2005 Added finding of R/O, System & Hidden files
  7. Public Function Exist(strFile As String, _
  8.                       Optional intAttrib As Integer = &H7) As Boolean
  9.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  10. End Function
  11.  
  12. 'CompactDb compacts a remote Access database.
  13. Public Function CompactDb(ByVal strDB As String, _
  14.                           Optional strPW As String = "") As Boolean
  15.     Dim strNewDB As String, strZip As String, strLocale As String
  16.  
  17.     On Error GoTo ErrorCDB
  18.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  19.     Call Echo(True, "Compacting """ & strDB & """.")
  20.     If strPW <> "" Then strLocale = ";pwd=" & strLocale
  21.     If Exist(strNewDB) Then Kill strNewDB
  22.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  23.                                   DstName:=strNewDB, _
  24.                                   DstLocale:=strLocale, _
  25.                                   SrcLocale:=strLocale)
  26.     Kill strDB
  27.     Name strNewDB As strDB
  28.     Call Echo(True, """" & strDB & """ compacted.")
  29.     CompactDb = True
  30.     Exit Function
  31.  
  32. ErrorCDB:
  33.     CompactDb = False
  34. End Function
This code can be added to a new module (I call mine modMain).
Feb 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
Hi Neo, I have added the code into the VBA, its seem every tables the sql run will prompt for "YES/NO". I had 20 over tables and it prompt for 20 overs times. Can I remore the prompt for this QUERY?

Thank
Expand|Select|Wrap|Line Numbers
  1. Private Sub  cmdDelRec_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. DoCmd.SetWarnings False
  6. strSQL = "DELETE * FROM "
  7. DoCmd.RunSQL strSQL & "Table1"
  8. DoCmd.RunSQL strSQL & "Table2"
  9. DoCmd.RunSQL strSQL & "Table3"
  10. DoCmd.SetWarnings True
  11.  
  12. End Sub
Feb 14 '07 #13

P: 11
One Q at a time ;)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Exist returns true if strFile exists.
  5. '22/05/2003 Rewritten with better code.
  6. '20/05/2005 Added finding of R/O, System & Hidden files
  7. Public Function Exist(strFile As String, _
  8.                       Optional intAttrib As Integer = &H7) As Boolean
  9.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  10. End Function
  11.  
  12. 'CompactDb compacts a remote Access database.
  13. Public Function CompactDb(ByVal strDB As String, _
  14.                           Optional strPW As String = "") As Boolean
  15.     Dim strNewDB As String, strZip As String, strLocale As String
  16.  
  17.     On Error GoTo ErrorCDB
  18.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  19.     Call Echo(True, "Compacting """ & strDB & """.")
  20.     If strPW <> "" Then strLocale = ";pwd=" & strLocale
  21.     If Exist(strNewDB) Then Kill strNewDB
  22.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  23.                                   DstName:=strNewDB, _
  24.                                   DstLocale:=strLocale, _
  25.                                   SrcLocale:=strLocale)
  26.     Kill strDB
  27.     Name strNewDB As strDB
  28.     Call Echo(True, """" & strDB & """ compacted.")
  29.     CompactDb = True
  30.     Exit Function
  31.  
  32. ErrorCDB:
  33.     CompactDb = False
  34. End Function
This code can be added to a new module (I call mine modMain).
Hi Neo,

Can I know where should the best place I call this module ? After I deleting the content tables and add it in the last row ? Why I saw in the script, there is looking existing database and deleting database?
Feb 14 '07 #14

P: 11
Sorry, can anyone help me on the above query ???

Thank you
Feb 14 '07 #15

NeoPa
Expert Mod 15k+
P: 31,494
Hi Neo,

Can I know where should the best place I call this module ? After I deleting the content tables and add it in the last row ? Why I saw in the script, there is looking existing database and deleting database?
That depends on when you want it to run.
You could call it from behind a button on your switchboard, or anywhere you can think about that would get it to run when you want it to.
Feb 14 '07 #16

P: 11
That depends on when you want it to run.
You could call it from behind a button on your switchboard, or anywhere you can think about that would get it to run when you want it to.
Sorry I tried to use the code you gave me put into the delete table content script and call modMain at the same time, but its promts error mention procedures error.
Do i do something wrong ?
?
Feb 15 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry I tried to use the code you gave me put into the delete table content script and call modMain at the same time, but its promts error mention procedures error.
Do i do something wrong ?
?
Post the script showing the calls.
Feb 15 '07 #18

NeoPa
Expert Mod 15k+
P: 31,494
You don't call modMain. You call CompactDB().
Feb 15 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.