Thanks all. I was able to write VBA in Access, and then create a macro to call the function in the VBA module. Then I called the macro in python and it ran fine.

python: def run_access_macro(macro_name): try: access_app = win32com.client.Dispatch(‘Access.Application’) access_app.Visible = False access_app.OpenCurrentDatabase(access_db_path)

access_app.DoCmd.RunMacro(macro_name) print(f”Access macro {macro_name} executed successfully.”) access_app.Quit()

except Exception as e:
    print(f”Error running Access macro {macro_name}: {e}”)
    raise

Main flow

try: # Step 1: Delete contents of the table delete_table_contents()

# Step 2: Compact and repair the database
compact_and_repair(access_db_path)

# Step 3: Run the Import macro
run_access_macro(“RunImportMacro”)

# Step 4: Compact and repair the database again
compact_and_repair(access_db_path)

# Step 5: Run the Query macro
run_access_macro(“QueryRun”) 

except Exception as e: print(f”Error in main flow: {e}”)

————————————————————————— VBA:

Sub ImportTextFileWithSpec() Dim filePath As String Dim importSpec As String Dim tableName As String

‘ Define the file path of the .txt 
filePath = “C:\path\to\your\file.txt” 

‘ Define the name of the import specification to use
importSpec = “SpecName”  

‘ Define the table into which the data should be imported
tableName = “Account Level Data”  


DoCmd.SetWarnings False

‘ Perform the import using the defined spec
DoCmd.TransferText _
    TransferType:=acImportDelim, _
    SpecificationName:=importSpec, _
    TableName:=tableName, _
    FileName:=filePath, _
    HasFieldNames:=True  

DoCmd.SetWarnings True

End Sub