Solved

Need help deleting the contents of a table

Posted on 2013-12-14
19
332 Views
Last Modified: 2013-12-20
Hello Experts:

I have an MS Access 2K application that use a split front end with a MS Access back end.
The application runs on an XP-pro platform.

I need to improve the performance of the following code which takes about 12 minutes to execute:

strSQLText="Delete Parts4.* from Parts4;"
Docmd.RunSql strSQLtext

I just want to delete the info in the table, nothing else.

Can anyone help?
0
Comment
Question by:pcalabria
  • 5
  • 5
  • 3
  • +4
19 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
drop table Parts4
create table parts4 (...)
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
...or use passthrough query.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Function RecreateTableStructure()
    Dim strPath As String
    strPath = "YourDB.mdb"
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, "Parts4", "MyTest1", True
    DoCmd.DeleteObject acTable, "Parts4"
    DoCmd.Rename "Parts4", acTable, "MyTest1"
End Function
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Yes, execute a pass-through query.

But try this first:

strSQLText="Delete * From Parts4;"
CurrentDb.Execute strSQLtext

/gustav
0
 

Author Comment

by:pcalabria
Comment Utility
Thanks, I'd like to try Emil_Gray's approach.
Cactus, I tried your code but it took 12.5 minutes to delete the contents of the table.

The problem I am having is that my code is running in the front end, and the table is in the backend.

Let's call the front end strPathFront and the backend table strPathBack

What I would like to run code from the current database located in the strPathFront database:

1.  Copy the structure of the Parts4 table in the backend database (strPathBack) to a temp table named Parts4Temp also located in the strPathBack database.
2.  Delete the Parts4 table from the back end database (strPathBack)
3.  Rename the Parts4Temp table located in the Back End Database (strPathBack) to "Parts4"
4. Compact the back end database (strPathBack)

I guess I just can't figure out the syntax!  :-)
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
I'm pretty sure that the easiest is passthrough query. You can create it following the steps in http://support.microsoft.com/kb/303968 .
0
 

Author Comment

by:pcalabria
Comment Utility
I think the problem is that I have a GB of data in the table, and MS Access is deleting everything one record at a time, so it takes time.

Deleting the object happens almost immediately, as doe copying the structure and renaming the structure.  The problem I am having is that the code is running in the front end, and I need to perform these operations in the back end.

I have limited knowledge of pass through queries.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Function RecreateTableStructure()
    Dim strPath As String
    strPath = "C:\My Documents\BEDatabase.mdb"
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, "Parts4", "MyTest1", True
    DoCmd.DeleteObject acTable, "Parts4"
    DoCmd.Rename "Parts4", acTable, "MyTest1"
End Function
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
> I have limited knowledge of pass through queries.

That's why we posted the link to MS Support article that gives step-by-step instructions.

When you realize that the code with RecreateTableStructure will work only with the local database, rather than with the linked backend, give it a try.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
FYI - pass-through queries apply to ODBC data sources such as SQL Server, NOT Jet/ACE.  Jet and ACE are file servers NOT database servers and NOTHING runs on the server EVER.  Everything you run against Jet/ACE runs in your own workspace on your own computer so even if you were to use OLE automation to run a query in the BE database, it is still running in memory on YOUR computer.

Given that, the best solution with temporary tables is to isolate them in their own database.  That way you can simply delete the database and recreate a new empty one.  Use Kill to delete the database file you want to get rid of.  I attached code I use in one app that creates an archive.  The attached code copies data from a number of tables and appends it to the newly created archive using TransferDatabase.  At the end a query is used to delete the audit that was archived.  The magic of cascade delete gets this done with a single delete to the audit table.  You didn't say how you were repopulating the table you are working with so I left my code in case it would be useful.

Sub CreateNewMDBFile()

    Dim ws As Workspace
    Dim db As Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strFileName As String
    Dim strPath As String
    Dim strDatapath As String

On Error GoTo Proc_Err
    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)

    'Path and file name for new mdb file

    If Forms!frmTPAName!txtPathName & "" = "" Then
        MsgBox "Please select destination folder for archive database.", vbOKOnly + vbInformation
        Forms!frmTPAName!txtPathName.SetFocus
        Exit Sub
    Else
        strPath = Forms!frmTPAName!txtPathName
        If Right(strPath, 1) = "\" Then
        Else
            strPath = strPath & "\"
        End If
    End If

    If Forms!frmTPAName!txtDatabaseName & "" = "" Then
        MsgBox "Please select destination folder for archive database.", vbOKOnly + vbInformation
        Forms!frmTPAName!txtDatabaseName.SetFocus
    Else
        strFileName = strPath & Forms!frmTPAName!txtDatabaseName
    End If
    'Make sure there isn't already a file with the name of the new database
    If Dir(strFileName) <> "" Then Kill strFileName

    'Create a new mdb file
    Set db = ws.CreateDatabase(strFileName, dbLangGeneral)
'    Set db = ws.OpenDatabase(strFilename, dbLangGeneral)
    Call ExportTables(strFileName)
   
    'update audit archive date in audit log
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs!qAuditLog
        qd.Parameters![enterauditparmsid] = Forms!frmLogin!cboAuditParmsID

    Set rs = qd.OpenRecordset
        rs.Edit
        rs!AuditArchiveDate = Date
        rs!AuditName = Forms!frmLogin!cboAuditParmsID.Column(2)
        rs!CoName = Forms!frmLogin!cboAuditParmsID.Column(3)
        rs!CoAbbr = Forms!frmLogin!cboAuditParmsID.Column(4)
        rs!UpdatedBy = Forms!frmLogin!txtUserID
        rs.Update
        
    rs.Close
    
    'Delete archived data from database
    DoCmd.OpenQuery "qDeleteArchivedAudit"
    
    MsgBox "Export Complete." & vbCr & " Your Archive is: " & vbCr & vbCr & strFileName, vbOKOnly + vbInformation
    
    db.Close
    Set db = Nothing
Proc_Exit:
    Exit Sub
Proc_Err:
    MsgBox Err.Number & "--" & Err.Description, vbCritical
    Resume Proc_Exit
End Sub

Public Sub ExportTables(strFileName)
    'Export data to new mdb file

    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportReviewStatus", "tblReviewStatus", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportStates", "tblStates", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportStatus", "tblStatus", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportRoles", "tblRoles", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportUserRoles", "tblUserRoles", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportUsers", "tblUsers", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportDocuments", "tblDocuments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportAuditParms", "tblAuditParms", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportMembers", "tblMembers", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportRefDocs", "tblRefDocs", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportComments", "tblComments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportDependents", "tblDependents", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportHelpComments", "tblHelpComments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportListValues", "tblListValues", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportLtrSent", "tblLtrSent", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportVerificationPhase", "tblVerificationPhase", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "tblTPA", "tblTPA", False
End Sub

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
The size limit of an Access DB from 2000 on is still 2GB. And when you delete either rows or objects they aren't actually out of the DB until you do a compact and repair. So if the table is deleted or the rows are deleted it and the data is about 1GB  that's half the DB. And that will slow down the processing as well.

You may want to consider going to SQL Express or some other solution.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
What if you take the backend database and run the same code within it, so it runs against its local table - will that be faster?

I actually tried it: put 30MB backend database on the share, and ran your code in frontend database across the network, deleting all records in linked table. It deleted 1M records absolutely instantly. So it does not look like it's deleting them one by one. 30MB is not 1GB, but I think the difference still would be there if that was the case.

If you see that the same query runs as slow in the backend database as well, it would mean that linked table issue has nothing to do with performance, and the time is spent, most likely, on paging.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
I have tested this with a backend database and there is no problem at all. Everything works perfectly. You will have to replace "accdb" with "mdb" if you are using Access 2003.

Function RecreateTableStructure()
    Dim tblName As String
    tblName = "TestData"
    Dim strPathBE As String
    strPathBE = "H:\Users\Emil Gray\Documents\Microsoft Access Databases\BEMyData.accdb"
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPathBE, acTable, "TestData", "MyTest1", True
    DoCmd.Rename "TestData", acTable, "MyTest1"
    With OpenDatabase("H:\Users\Emil Gray\Documents\Microsoft Access Databases\BEMyData.accdb")
    .Execute "DROP TABLE [" & tblName & "]"
    End With
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPathBE, acTable, "TestData", "TestData", True
    DoCmd.DeleteObject acTable, "TestData"
End Function
0
 
LVL 8

Accepted Solution

by:
Emil_Gray earned 500 total points
Comment Utility
I have tested this with a backend database and there is no problem at all. Everything works perfectly. You will have to replace "accdb" with "mdb" if you are using Access 2003.

Function RecreateTableStructure()
    Dim tblName As String
    tblName = "TestData"
    Dim strPathBE As String
    strPathBE = "H:\Users\Emil Gray\Documents\Microsoft Access Databases\BEMyData.accdb"
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPathBE, acTable, "TestData", "MyTest1", True
    DoCmd.Rename "TestData", acTable, "MyTest1"
    With OpenDatabase("H:\Users\Emil Gray\Documents\Microsoft Access Databases\BEMyData.accdb")
    .Execute "DROP TABLE [" & tblName & "]"
    End With
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPathBE, acTable, "TestData", "TestData", True
    DoCmd.DeleteObject acTable, "TestData"
End Function
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this short codes
> change "table1" with the actual name of your table
> change  (CurrentProject.Path & "\BEdatabase.accdb")  with the actual path and name of your BE db.

Sub DeleteRecords()
Dim accApp As Object
Set accApp = CreateObject("access.application")
accApp.OpenCurrentDatabase (CurrentProject.Path & "\BEdatabase.accdb")
    accApp.DoCmd.RunSQL "select table1.* into table2 from table1 where 1=0"
    accApp.RefreshDatabaseWindow
    accApp.DoCmd.RunSQL "drop table table1"
    accApp.DoCmd.Rename "table1", acTable, "Table2"
accApp.CloseCurrentDatabase
Set accApp = Nothing

End Sub
0
 

Author Closing Comment

by:pcalabria
Comment Utility
Thanks all for your help.  The solution by Emil worked perfectly, except minor modifications needed to be made.  I did not use the rename statement, as renaming the imported table to use the same name as the link to the table caused an error.  Instead, I imported the table as a TEMP table, then renamed it when importing back into the BE.

Thanks.  Excellent.

One other note, Cap, I did not have a chance to test your solution has I had already experienced success with the code provided by Emil.  Thanks for the help...  You Experts are great!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@pcalabria

you should test all suggested solution to your problem.

the code i posted will do exactly what you wanted to do in the most efficient way,
and you don't have to do anything else if you follow the instruction.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Hey Cap he threw me a bone and I appreciate it. I didn't test your solution but I'm sure it will work just as you said. Have a great day.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now