Rob,
I tried returning your zip file in which I have added my copy of your database. Mine is called RobSmithCopyJ.accdb. It had all the materials below. Table W, basMod etc. BUT CAN NOT GET ANY ZIP FILES TO ATTACH ON THIS SITE (not getting any error, just doesn't attach)
I have attached it here <<<<<<<<<<<<<<
I have created an automated routine in module basMod to do the import of the matches csv you sent, then do the convert of the Date.
The routine is dependent on an Import Specification called "OrangeImportSpecification" and uses my csv file name and is imported into table W. You can change the file name and the table name to suit your needs.
I created the Import Specification during the import and reference it in the routine.
This code was used to test the import, convert the date 10 times.
Code:
' ----------------------------------------------------------------
' Procedure Name: testit
' Purpose: Routine to exercise the import matches csv, convert the Date 10 times
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 01-Mar-20
' ----------------------------------------------------------------
Sub testit()
Debug.Print "Starting a session of 10 import/deletes"
Dim i As Integer
i = 10
For i = 1 To 10
ImportMatches
Next i
Debug.Print "Finished the session of 10 import/deletes"
End Sub
Below is the code that Deletes the contents of table W, then does the import and Convert Date.
You can remove or comment these lines from the routine.
Debug.Print "Deleting current contents of table W @ " & Now
CurrentDb.Execute "Delete * from W;", dbFailOnError
Here is the ROUTINE ImportMatches
Code:
' ----------------------------------------------------------------
' Procedure Name: ImportMatches
' Purpose: Routine to empty table W, then import to table W, then convert the Date.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 01-Mar-20
' ----------------------------------------------------------------
Sub ImportMatches()
On Error GoTo ImportMatches_Error
Debug.Print "Deleting current contents of table W @ " & Now 'can be removed
CurrentDb.Execute "Delete * from W;", dbFailOnError 'can be removed
DoEvents 'a short break to the OS
Debug.Print "Starting to process the Matches csv file @ " & Now
DoCmd.TransferText acImportDelim, "OrangeImportSpecification", "w", "c:\users\jack\documents\Matches For Orange.csv", True
Debug.Print "Finished importing the csv"
DoEvents 'a short break to the OS
CurrentDb.Execute "UPDATE w SET w.ConvDate = CDate(Date)", dbFailOnError
Debug.Print "Finished updating the Date on table W"
Debug.Print "All done @ " & Now
On Error GoTo 0
ImportMatches_Exit:
Exit Sub
ImportMatches_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMatches, line " & Erl & "."
GoTo ImportMatches_Exit
End Sub
Here is the output from session to exercise the ImportMatches routine 10 times.
Code:
Starting a session of 10 import/deletes
Deleting current contents of table W @ 01-Mar-20 6:33:08 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:08 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:09 PM
Deleting current contents of table W @ 01-Mar-20 6:33:09 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:09 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:10 PM
Deleting current contents of table W @ 01-Mar-20 6:33:10 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:10 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:11 PM
Deleting current contents of table W @ 01-Mar-20 6:33:11 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:11 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:12 PM
Deleting current contents of table W @ 01-Mar-20 6:33:12 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:12 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:13 PM
Deleting current contents of table W @ 01-Mar-20 6:33:13 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:13 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:14 PM
Deleting current contents of table W @ 01-Mar-20 6:33:14 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:14 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:15 PM
Deleting current contents of table W @ 01-Mar-20 6:33:15 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:15 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:15 PM
Deleting current contents of table W @ 01-Mar-20 6:33:15 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:15 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:16 PM
Deleting current contents of table W @ 01-Mar-20 6:33:16 PM
Starting to process the Matches csv file @ 01-Mar-20 6:33:16 PM
Finished importing the csv
Finished updating the Date on table W
All done @ 01-Mar-20 6:33:17 PM
Finished the session of 10 import/deletes
Good luck.