Solved

Need help deleting the contents of a table

Posted on 2013-12-14
19
338 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
ID: 39719536
drop table Parts4
create table parts4 (...)
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39719561
...or use passthrough query.
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 39719564
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39719657
Yes, execute a pass-through query.

But try this first:

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

/gustav
0
 

Author Comment

by:pcalabria
ID: 39720026
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
ID: 39720041
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
ID: 39720110
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
ID: 39720142
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
ID: 39720165
> 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
 
LVL 36

Expert Comment

by:PatHartman
ID: 39720204
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.
ID: 39720245
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
ID: 39720317
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
ID: 39720330
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
ID: 39720521
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39720585
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
ID: 39730948
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39730959
@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
ID: 39730980
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

829 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