Macro To Run Saved Imports and Code (1 Viewer)

[robsmith]

Hi,

I am trying to put together a macro to run various saved imports followed by some code. I have done the saved imports and they work but I can't seem to get the code to run.

The code is in a module and I have included it below. Can anyone point me in the right direction?

Thanks

Option Compare Database
Option Explicit

Sub ImportMatches()
10        On Error GoTo ImportMatches_Error
20    Debug.Print "Deleting current contents of table Matches @ " & Now
30    CurrentDb.Execute "Delete * from Matches;", dbFailOnError
40    DoEvents  'a short break to the OS
50    Debug.Print "Starting to process the Matches csv file @ " & Now
60    DoCmd.TransferText acImportDelim, "OImportSpecification", "Matches", "E:\Documents\Matches.csv", True
70    Debug.Print "Finished importing the csv"
80    DoEvents 'a short break to the OS
90    CurrentDb.Execute "UPDATE Matches SET Matches.ConvDate = CDate(Date)", dbFailOnError
100   Debug.Print "Finished updating the Date on table Matches"
110   Debug.Print "All done @ " & Now
      
120       On Error GoTo 0
ImportMatches_Exit:
 DoCmd.Close acTable, "wMatches"
 'DoCmd.CloseDatabase   'commented out the close database
130       Exit Sub

ImportMatches_Error:
140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMatches, line " & Erl & "."
150       GoTo ImportMatches_Exit
End Sub

 

[Gasman]

So what does it do/not do?

 

[arnelgp]

do you import from same csv to same table?
if so you can use the "ImportExportSpecification" itself to execute:

dim oSpec As ImportExportSpecification
Set oSpec = CurrentProject.ImportExportSpecifications.Item("OImportSpecification")
oSpec.Execute
Set oSpec = Nothing

 

[Gasman]

I seem to recall there are two type of named specifications.?
One is that run by arnelgp's code or using DoCmd.RunSavedImportExport

Using the TransferText method you need to save as per this link https://datatofish.com/create-specification-name-access-2016/

HTH