Import assistance

[Michael Abrams]

Happy New Year to all (and make it a HEALTHY one too!)

These are the kind of things that push me to retirement.
Same code, same files etc for 15 years. Today -ACK!

Code: Select all

Option Compare Database
Option Explicit

Private Sub cmdImportFiles_Click()

Dim strMonth As String
strMonth = InputBox("Enter 3 letter abbreviation for month & 4 digit year. (NO HYPHEN) eg: APR 2024")
If Not Len(strMonth) = 8 Then
MsgBox "Invalid - try again.", vbExclamation
Exit Sub
End If

Dim strFilename As String
strFilename = InputBox("Enter filename NO HYPHEN) eg: 20240508")
If Not Len(strFilename) = 8 Then
MsgBox "Invalid - try again.", vbExclamation
Exit Sub
End If

DoCmd.TransferText acImportFixed, "CAP 2004", "HOSP(SEBMF-DD)CAP", _
"R:\SEBMF HMOs 2024\HEALTHNET 2024\" & strMonth & "\TEXT FILES\HOSPITAL FILES\ACE_RPT_BRM_12BSEQ_M4504_" & strFilename & "_M_S.txt", False, ""                                                                              
MsgBox "FILES HAVE BEEN IMPORTED", , "IMPORT COMPLETE"

DoCmd.Close acForm, Me.Name

End Sub

Path:
R:\SEBMF HMOs 2024\HEALTHNET 2024\JAN 2024\TEXT FILES\HOSPITAL FILES

Report name:
ACE_RPT_BRM_12BSEQ_M4504_20240131_M_S.TXT

Month Input box Jan 2024

Filename Input box:
20240131

ERROR 3051
I do have a print screen but it is not allowing me to add the file. says HTTP error.

[HansV]

Although it shouldn't matter, does it help if you remove

, ""

from the end of the DoCmd.TransferText line?

And are you sure that the text file is not open in another application?

[Michael Abrams]

I STILL can't stand my IT department. SSDD with them.

I insisted they review my drive permissions.
After lengthy MS teams chats and shared screens. they saw I was not at fault with anything.
Sure enough, they "updated" the permissions.

And of course, no explanation regarding how it happened.

[HansV]

Well, at least it works again...