Results 1 to 12 of 12
  1. #1
    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?

  2. #2
    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

  3. #3
    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....

  4. #4
    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.....

  5. #5
    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.


  6. #6
    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

  7. #7
    Join Date
    Nov 2004
    Posts
    90
    HELLO,

    can you show me the step to create the delete query? thanks for ur help.

  8. #8
    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?

  9. #9
    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

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,000
    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

  11. #11
    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...

  12. #12
    Join Date
    Nov 2004
    Posts
    90
    hello,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •