Strange behaviour in MS Access: Delete Table with mouse != Delete Table with VBA

Question

I need some help with VBA in MS Access! Following setup: There is a table "tblTablesToLink":

ID	FileName	TableName	ImportSpecification
1	SL100.txt	raw_ReadData	SL2100-Import
2	objects.txt	raw_Coordinates	Coordinate-Import

I have set up an AutoExec in Access that contains a lot of code. After starting Access, I want:

1. Get the current Project Path for the access database
2. Load some txt files as linked tables into access
3. Convert the linked tables to local tables for further calculations

There will be more - just ignore it for now.

Public Function StartUp()
    Dim strPathToFiles As String
    Dim myDB As DAO.Database
    Dim rstFilesToLink As DAO.Recordset

    strPathToFiles = CurrentProject.Path & "\RawData\"
    Set myDB = CurrentDb

    ' Link Data to Access Database ====================
    Set rstFilesToLink = myDB.OpenRecordset("tblTablesToLink", dbReadOnly)
    
    With rstFilesToLink
        Do While Not .EOF
            DoCmd.DeleteObject acTable = acDefault, ![TableName]
            DoCmd.TransferText _
            TransferType:=acLinkDelim, _
            SpecificationName:=![ImportSpecification], _
            TableName:=![TableName], _
            FileName:=strPathToFiles & ![FileName], _
            HasFieldNames:=False
            .MoveNext
        Loop
    End With
    rstFilesToLink.Close
    Set rstFilesToLink = Nothing
    ' =================================================

    ' Convert linked data into local tables ===========
    DoCmd.SelectObject acTable, "raw_ReadData", True
    RunCommand acCmdConvertLinkedTableToLocal
    DoCmd.SelectObject acTable, "raw_Coordinates", True
    RunCommand acCmdConvertLinkedTableToLocal
    ' =================================================
End Function

So my code is running w/o errors exactly one time. Access is linking the two txt files and converts them into local tables. Re-running this function will just link the txt files w/o converting. I've also added DoCmd.DeleteObject acTable = acDefault, raw_ReadData and DoCmd.DeleteObject acTable = acDefault, raw_Coordinates at the beginning of my function just to be sure.

Nothing changes.

I have to re-run the code a second time to convert the tables again. So I need two runs of the same code...

It will work as expected multiple times if I'm deleting both tables by hand via right mouse click in the Access window and re-running the code. WTF?! So "DeleteObject" isn't the same as "Delete"? What am I doing wrong? Or is there another simple way to load txt data in a local table?

Answer 1

Instead of deleting the table each time, import the text and archive the text file. This way you aren't processing a growing text file, you are only adding new data.

'@Description("ISO Compliant file timestamp")
Public Function ISOTimeStamp(Optional ByVal zulu As Boolean) As String
Attribute ISOTimeStamp.VB_Description = "ISO Compliant file timestamp"
    If zulu Then
        ISOTimeStamp = Format$(ConvertUTC(Now), "yyyy-MM-ddThhmmssZ")
    Else
        ISOTimeStamp = Format$(Now, "yyyy-MM-ddThhmmss")
    End If
End Function



Public Function StartUp()
    Dim strPathToFiles As String
    Dim myDB As DAO.Database
    Dim rstFilesToLink As DAO.Recordset

    strPathToFiles = CurrentProject.Path & "\RawData\"
    Set myDB = CurrentDb

    ' Link Data to Access Database ====================
    Set rstFilesToLink = myDB.OpenRecordset("tblTablesToLink", dbReadOnly)
    
    With rstFilesToLink
        Do While Not .EOF
            Dim originalFile As String
            originalFile = strPathToFiles & ![FileName]

            ' Clear the old data out of the table
            Docmd.RunSQL “DELETE * FROM " & ![TableName] & ";”

            ' Load fresh data from the text file
            DoCmd.TransferText _
            TransferType:=acImportDelim, _
            SpecificationName:=![ImportSpecification], _
            TableName:=![TableName], _
            FileName:=originalFile, _
            HasFieldNames:=False

            Dim archiveFile As String
            archiveFile = CurrentProject.Path & "\ImportedData\" & ![FileName] & ISOTimeStamp

            ' Move the file to archive
            Name originalFile As archiveFile

            .MoveNext
        Loop
    End With

    rstFilesToLink.Close
    Set rstFilesToLink = Nothing
End Function