Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing records or run Delete Query (Access 2000)

    I'm trying to clean a table. At the moment I'm running the following code to clear the data. My question is would it be quicker to go through the records one by one and then delete the record if it meets the criteria. My code is:

    Function ClearAllLines()

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*START*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*[*]*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE AMOUNT Like ""*PAGE*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE AMOUNT Like ""*LEDGER TRANSACTION LISTING REPORT*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*NNMLMR04*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE AMOUNT Like ""*FINANCIAL AMOUNT*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*SOURCE*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*=*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE EFF_DATE=""00/00/00"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Is Null AND AMOUNT Is Null;"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Not Like ""*ACCOUNT:*"" AND AMOUNT Is Null;"
    'DoCmd.RunSQL "ALTER TABLE " & tableName & " ADD COLUMN Account text, BrNo text;"
    DoCmd.RunSQL "UPDATE " & tableName & " SET Account = Right([source],3) & Left([jnl_desc],3)WHERE SOURCE Like ""*Account*"";"
    DoCmd.RunSQL "DELETE * FROM " & tableName & " WHERE SOURCE Like ""*Account:*"";"
    DoCmd.RunSQL "UPDATE " & tableName & " SET BrNo = [jNL_DESC] WHERE Right([Account],6)=Mid([source],3,6);"
    DoCmd.SetWarnings True
    End Function

    At the moment it takes quite a while to access the table (Records = 3.5Mil) Any help would be appreciated

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    I presume you are not really deleting all the data in the table, but just records with the selected criteria. If that is the case, you could combine several of the WHERE clauses into a single DELETE query - running multiple DELETE queries of the sort you have is bound to take a while with that many records. In additiion the ALTER and UPDATE statements are generally much more resource intensive than DELETEs, so they may be contributing a fair bit of your execution time. Just out of curiosity, is this an Access back-end with 3.5 million records? If so, the workstation you are running on should have GOBS of RAM, or you will be swapping data on the hard drive like crazy. Hope this helps.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    Thanks for the advise. Won't it be better to write a query to create a new table while excluding all the delete criteria? My PC is a 2.4 with 1 Gig of memory and it still takes 15 minutes per Delete Query.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing records or run Delete Query (Access 2000)

    One of your problems is that LIKE is a *very* slow operator on a large recordset. You might be ahead to use a function like InStr to determine if the expression is found in the field.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    So you are saying that if I rather say mid([Source],2,10) = "Transaction" it should run faster. What will the syntax be for Instr if I'm looking for the transaction value.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing records or run Delete Query (Access 2000)

    No, I'm saying that using a calculated expression like

    <pre>Instr([Source],"Transaction")>0</pre>


    will return a true or false for each record and will be faster then LIKE. If you set the criteria under that expression to True, then only the records where Source contains the word Transaction will be affected.

    Using a Mid function would require that you to know in advance exactly *where* in the string the word or phrase was going to occur.

    If you want to build SQL, it would be something like this:

    <pre>"DELETE * FROM " & tableName & " WHERE InStr(" & tableName & ".SOURCE, 'Transaction') > 0"</pre>


    I don't understand the last question.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    If you are processing a large amount of records using RunSQL method, you may want to set the optional second argument, Use Transaction, to False. The default is True. From Help:

    UseTransaction - Optional Variant. Use True (

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    Thanks MarkD for this info. I will try it

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    My point was that the amount of time each delete query takes is primarily a function of the number of records that have to be scanned - if you combine the WHERE clauses, the scan only happens once - obviously it takes a bit longer to evaluate each record, but you might find that the entire set of DELETE queries runs in 30 minutes instead of running 10 or so queries each taking 15 minutes. When all is finished, how many records remain of the 3.5 million you start with? Also, how does the table get built - are you importing a large text file, or is this a permanent table of some sort?
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    I did understand your ideas. If I'm "lucky" about 2 mil is left. The table is imported/created from an text file. They printed an Financial Ledger to a word file. With a lot of sweat I managed to change the file to txt and now I'm importing the file into Access and the trying to delete all the unnecessary data.

    Thanks

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    That actually sounds like they printed the Financial Ledger report to a file - done commonly in the old DOS days. Still a big file - if I calculated right it is the equivalent of about 58,000 pages. My partner worked on some stuff like this where they were dumping huge files out of their accounting system, and then doing analysis on them in Access. One other thing you might think about is putting an index on the Transaction and Account fields once you've sucked it into Access. That would take a while, but should allow you to run queries and deletes much faster. You might also want to consider editing the file in Word (or some other editor) to replace things that are text fields where you are doing compares with something that is numeric and would give a quick index.
    Wendell

  12. #12
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    Mario,

    I am doing something similiar. I take the enacted NYS Budget Bills and have then in a text format. I read in the text file using a VBA module and and select the lines that I want for the database. when I find a line that matches the criteria i add it to my table. In your case it would seem you could write the 'instr' for each of your delete lines and if one is found just read the next line.

    If you would like to see what I have done let me know and I'll send you the code for my module.

    Don
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  13. #13
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    Thanks for the news Wendell. I thought I was the only succer.

    Don if you could send some of your code that would be great

    Thanks

  14. #14
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing records or run Delete Query (Access 2000)

    Mario,

    I have attached the entire module in a text file. I use a form that allows for entering the path to the Bill file, the Bill Name, fiscal year and the year enacted. A button is then clicked to run the module. Another form is opened to display where I am in the process showing the page number I'm on, the number of lines read and the number of rows written. This is so the user knows something is happening.

    Don Lansing
    bdklans@budget.state.ny.us
    Attached Files Attached Files
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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