I'm currently attempting to write a small Access Database which carries out the following tasks :
1. Imports Submission files (of a text variety) to a table using TransferText
on a daily basis (many files into 1 table)
2. Imports Reject Files files (again of a text variety) to another table using Transfer Text. (again, daily, many files into 1 table)
3. Run a query which reports all submissions that haven't had a matching rejection.
(I've done a small amount of moving of files around and renaming when archiving, but that's not particularly relevant to my question).
Now the tricky part (well for me at least!). The files which I'm importing for step 2 above contain a date field, which is essential to the matching process that I need to carry out in step 3. The date format that is received is a Julian date without the year of the format NNN - eurghh. For example January 15th 2007 would be represented as 015 (as would any January 15th!).
What I need to do is one of the following :
reformat/translate this as part of the DoCmd.TransferTxt as it's imported
(I don't think this is possible!?)
or add another field which represents the date in a more usable format eg DD/MM/YY during the import process. (not sure how I'd do this)
or Add a command at the end of my import process which runs through each of the records and adds the relevant date field whilst copying it to the full repository table.
Thanks for taking the time to look at this!
Full code for step 2 is below this is driven through a button :
Private Sub Command12_Click()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
strFolderPath = "C:\TESTDB\Reject\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
For Each objF1 In objFiles
If InStr(objF1.Name, "msg") > 0 Then
'Variables used to create and modify the file extension
Dim objFileSystem
Dim objFile
Dim strFileCopy As String
Dim strFileCopy2 As String
Dim intExtPosition As Integer
'Create an instance of the FileSystemObject to access
'the local file system
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.GetFile(objF1)
intExtPosition = InStr(objFile.Name, ".")
If intExtPosition > 0 Then
strFileCopy2 = Left(objFile.Name, intExtPosition - 1)
Else
strFileCopy2 = objFile.Name
End If
strFileCopy = strFileCopy2 & ".txt"
'Create a copy of the file with a .txt extension
objFile.Copy strFolderPath & strFileCopy, True
DoCmd.TransferText acImportFixed, "XYZ Export Specification", "AccRejects", strFolderPath & strFileCopy, False
MsgBox strFolderPath & objF1.Name & " loaded"
Name strFolderPath & objF1.Name As "C:\TESTDB\Reject\Archive\" & objF1.Name 'Move the files to the archive folder
Kill strFolderPath & strFileCopy
End If
Next
Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
bImportFiles_Click_Exit:
Exit Sub
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub