If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to delete all records in table using macro?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
Cool How to delete all records in table using macro?

Which action command should I use to deleta all records in a table by using macro command?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Charlotte, NC
Posts: 164
Why do you want to use a Macro Command? A Delete Query will accomplish this.

TD
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
because I wanna to assign it in command button which can act two function simultenously delete all existing record and import data from excell.If query can act this two function need advice on it....
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 34
Just have a macro run the two delete queries, or have code behind the button run the two queries, or you could do it all with code.....
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Sussex, England
Posts: 404
Gotta agree with jammybasturd on this. If you must delete all the records in a table use code in a function called by a button.

I have tables which are populated for letters and similar things and use this method. It's handy if yu later decide you need to add a specific warning ("You are deleting all your old letters. Have a nice day!") or check that there is something to repopulate the table with.

Calling queries is a good method to use. I used to write code to do the delete, but calling the queries makes the whole thing a bit easier when you're updating Access.

Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Charlotte, NC
Posts: 164
Create a delete query to delete all of the records. Then create a Macro that you can attach to a button on your form. In the Macro, SetWarnings to Off so that it doesn't require you to say OK when it is ready to delete the records. On the 2nd line, do an OpenQuery for the delete query that you just wrote. Then on the next line do a TransferSpreadsheet to import the new data. Then SetWarnings to On on the next line. Do not forget to do this last step.

TD
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
HELLO,

can you show me the step to create the delete query? thanks for ur help.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
in the open query macro I was set to query that I was wrote, I dont understand what action to be set in view and data mode in that open query.In transfer spreadsheet macro, file name is stand for what? is it for place I stored my excell?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Charlotte, NC
Posts: 164
For the Macro, when you put the OpenQuery line in, the Query Name box should have the name of the DeleteQuery that you just wrote, the View should be Datasheet, and the DataMode should be Edit.

On the next line down, when you put the TransferSpreadsheet line in, The TransferType is Import, the TableName is the name of the table that you just ran the delete query against and you now want to populate, the FileName is the full path and name of the spreadsheet that you want to import, HasFieldNames should say yes if you have column headings in your spreadsheet, and Range should have the name of the Tabbed Worksheet in the Excel file you are importing.

TD
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Location: Dallas, TX
Posts: 996
Hi oasislah,

What you should do is create your Query, and then run it from a Module wiht a CommandButton. See this below as the code I have attached to a CommandButton to run 2 queries. Done the way that it is, there is no need to use the SetWarnings On/Off, as so many people just forget to set them back on again. Here the [color = green]CurrentDB.Execute[/color] takes care of all that for you.
Code:
[color = BLUE]
Private Sub cmbProcessJobCharge_Click()  'The name of the CommandButton.
On Error GoTo Err_cmbProcessJobCharge_Click
'This Prints out the job charge report,saves the records and closes the form'
    Dim stDocName As String

    stDocName = "JobCharge"
    DoCmd.GoToRecord , , acNewRec
    JobID.SetFocus
    DoCmd.OpenReport stDocName, acNormal
    
    stDocName = "LaunchPad.PostJobCharge"
    CurrentDb.Execute "PostJobCharge"   ‘This is One Query to run.
    CurrentDb.Execute "UpdateStockedItems"   ‘This is the Second Query to run.
    
    DoCmd.Requery "sfrmJobChargesViewAll"
    DoCmd.Requery "JobID"
    'DoCmd.Close
[/color]
One easy way to start creating your own VBA code is to go ahead and create a Macro. THEN, in DesignView of that Macro, click SaveAs, and click the ComboBox arrow to select Module. What happens is that it will create the VBA code for you in a module. Next, look at the Code that was created for you. That is how I learned...but since then I never ever create Macros at all.
When you finish having that done, you simply do the same as you do when attaching a Macro to a CommandButton. Only, when you are attaching, just scroll in the ComboBox to select the VBA Module you created. IF you save it with a very descriptive name you will find it. What I would do is name it something like modDeleteQuery that way you can see right off it is a Module.

hope this helps you out my friend,
have a nice one,
BUD
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
Thanks a lot for u two Buckeye and Bud,

Right now I wanna try to use this advice and hope it will work well..Now for running my time I need an idea from someone in create BT report. in BT report I need one field of a table to increase one.For result, the last record of field name will be printed in report.

For easy way to understand hopefully:

1) table "sts_BT" - field name "typelastNoprinted"
2) table "trans1" - field name "to warehouse"

when user enter one of warehouse code in fieldname 'to warehouse' then field name 'typelastNoPrinted' will increase to one. There are many kind of warehouse code. So each warehouse code has their typelastNoprinted record. any Idea please suggest...
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 90
hello,

anyone have an idea on above problem? if so please give an idea/suggest please..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On