Accessing records or run Delete Query (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Instr([Source],"Transaction")>0

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:

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

I don’t understand the last question.

Reply | Quote

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

Instr([Source],"Transaction")>0

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:

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

I don’t understand the last question.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 (

Reply | Quote

Thanks MarkD for this info. I will try it

Reply | Quote

Thanks MarkD for this info. I will try it

Reply | Quote

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 (

Reply | Quote