Need help deleting the contents of a table

drop table Parts4
create table parts4 (...)

...or use passthrough query.

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

Yes, execute a pass-through query.

But try this first:

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

/gustav

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!  :-)

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

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.

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

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

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

Select allOpen in new window

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.

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.

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

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

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!

@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.

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.