I have a module that imports data from text files and then I need to update the data. But I only want to update the new data that is imported.
The module is as follows:
what changes do I need to make?
Dim Create As Database, wl_import As Recordset, wl_cmds As Recordset, int1 As Long, int2 As Long, i As Integer, str_answer As String
Dim intAnswer As String
Dim str_filename As String
Dim MyDir As String
Dim MyFile As String
Dim MyArray() As String
Dim FileCounter As Integer
Dim str_censusdate As String
Dim strSQL As String
Set Create = DBEngine.Workspaces(0).Databases(0)
Set wl_import = Create.OpenRecordset("WL CMDS (RNA00)"
Set wl_cmds = Create.OpenRecordset("WL NonRes"
MyDir = "K:\WL CMDS (Current Financial Year)\Good Hope Hospital NHS Trust\Inpatient Waiting List CMDS\"
intAnswer = InputBox("What month?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is in the abbreviated format ""Jan"" for January", vbInformation, "Select Month"
Select Case intAnswer
Case "Apr": str_filename = "01 IWL Apr*.txt"
Case "May": str_filename = "02 IWL May*.txt"
Case "Jun": str_filename = "03 IWL Jun*.txt"
Case "Jul": str_filename = "04 IWL Jul*.txt"
Case "Aug": str_filename = "05 IWL Aug*.txt"
Case "Sep": str_filename = "06 IWL Sep*.txt"
Case "Oct": str_filename = "07 IWL Oct*.txt"
Case "Nov": str_filename = "08 IWL Nov*.txt"
Case "Dec": str_filename = "09 IWL Dec*.txt"
Case "Jan": str_filename = "10 IWL Jan*.txt"
Case "Feb": str_filename = "11 IWL Feb*.txt"
Case "Mar": str_filename = "12 IWL Mar*.txt"
Case Else: Exit Function
End Select
'now loop for the matching files
MyFile = Dir(MyDir & str_filename, vbNormal)
Do While (MyFile <> ""
FileCounter = FileCounter + 1
ReDim Preserve MyArray(1 To FileCounter)
MyArray(FileCounter) = MyDir & MyFile
MyFile = Dir 'Sorry missed this bit off the original code
Loop
'now iterate through the created array an import files one by one
If FileCounter > 0 Then
For i = 1 To FileCounter
DoCmd.TransferText acImportDelim, "IPWL 04 RNA Import", "WL CMDS (RNA00)", MyArray(i)
Next i
End If
Select Case intAnswer
Case "Apr": str_censusdate = "20030430"
Case "May": str_censusdate = "20030531"
Case "Jun": str_censusdate = "20030630"
Case "Jul": str_censusdate = "20030731"
Case "Aug": str_censusdate = "20030831"
Case "Sep": str_censusdate = "20030930"
Case "Oct": str_censusdate = "20031031"
Case "Nov": str_censusdate = "20031130"
Case "Dec": str_censusdate = "20031231"
Case "Jan": str_censusdate = "20040131"
Case "Feb": str_censusdate = "20040228"
Case "Mar": str_censusdate = "20040331"
Case Else: Exit Function
End Select
strSQL = "UPDATE [WL CMDS (RNA00)] SET Census_Date = '" & str_censusdate & "' WHERE Census_Date <> '" & str_censusdate & "'"
DoCmd.RunSQL strSQL
End Function
The module is as follows:
what changes do I need to make?
Dim Create As Database, wl_import As Recordset, wl_cmds As Recordset, int1 As Long, int2 As Long, i As Integer, str_answer As String
Dim intAnswer As String
Dim str_filename As String
Dim MyDir As String
Dim MyFile As String
Dim MyArray() As String
Dim FileCounter As Integer
Dim str_censusdate As String
Dim strSQL As String
Set Create = DBEngine.Workspaces(0).Databases(0)
Set wl_import = Create.OpenRecordset("WL CMDS (RNA00)"
Set wl_cmds = Create.OpenRecordset("WL NonRes"
MyDir = "K:\WL CMDS (Current Financial Year)\Good Hope Hospital NHS Trust\Inpatient Waiting List CMDS\"
intAnswer = InputBox("What month?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is in the abbreviated format ""Jan"" for January", vbInformation, "Select Month"
Select Case intAnswer
Case "Apr": str_filename = "01 IWL Apr*.txt"
Case "May": str_filename = "02 IWL May*.txt"
Case "Jun": str_filename = "03 IWL Jun*.txt"
Case "Jul": str_filename = "04 IWL Jul*.txt"
Case "Aug": str_filename = "05 IWL Aug*.txt"
Case "Sep": str_filename = "06 IWL Sep*.txt"
Case "Oct": str_filename = "07 IWL Oct*.txt"
Case "Nov": str_filename = "08 IWL Nov*.txt"
Case "Dec": str_filename = "09 IWL Dec*.txt"
Case "Jan": str_filename = "10 IWL Jan*.txt"
Case "Feb": str_filename = "11 IWL Feb*.txt"
Case "Mar": str_filename = "12 IWL Mar*.txt"
Case Else: Exit Function
End Select
'now loop for the matching files
MyFile = Dir(MyDir & str_filename, vbNormal)
Do While (MyFile <> ""
FileCounter = FileCounter + 1
ReDim Preserve MyArray(1 To FileCounter)
MyArray(FileCounter) = MyDir & MyFile
MyFile = Dir 'Sorry missed this bit off the original code
Loop
'now iterate through the created array an import files one by one
If FileCounter > 0 Then
For i = 1 To FileCounter
DoCmd.TransferText acImportDelim, "IPWL 04 RNA Import", "WL CMDS (RNA00)", MyArray(i)
Next i
End If
Select Case intAnswer
Case "Apr": str_censusdate = "20030430"
Case "May": str_censusdate = "20030531"
Case "Jun": str_censusdate = "20030630"
Case "Jul": str_censusdate = "20030731"
Case "Aug": str_censusdate = "20030831"
Case "Sep": str_censusdate = "20030930"
Case "Oct": str_censusdate = "20031031"
Case "Nov": str_censusdate = "20031130"
Case "Dec": str_censusdate = "20031231"
Case "Jan": str_censusdate = "20040131"
Case "Feb": str_censusdate = "20040228"
Case "Mar": str_censusdate = "20040331"
Case Else: Exit Function
End Select
strSQL = "UPDATE [WL CMDS (RNA00)] SET Census_Date = '" & str_censusdate & "' WHERE Census_Date <> '" & str_censusdate & "'"
DoCmd.RunSQL strSQL
End Function
