J
jslick76
Can anyone look at my code and tell me what's wrong?
It all works fine until the last step which is docmd.transfertext exporting a
variable table name
At the end when i try to export my file, i get a run time error 3027 message.
FileNamePart is a function that just extracts 'filename' out of C:\temp\
filename.txt
thanks!
Sub e()
'Set Variables
Dim strSQL As String
Dim strFileName As String
Dim strFileName2 As String
'Don't display a bunch of messages
DoCmd.SetWarnings False
'prompt for a filename to import
strFileName2 = GetFile("txt:*.txt", "z:\mail\output", "Import your
XXXXXall.txt file")
'import that file
DoCmd.TransferText acImportDelim, "All Import Specification",
FileNamePart(strFileName2), strFileName2, False
'Add an autonumber
DoCmd.RunSQL "ALTER Table " & (FileNamePart(strFileName2)) & " ADD
Column ID counter"
'Update SEQ field15
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field15 = id & ' / ' & field15"
DoCmd.RunSQL strSQL
'Update brk, ctn, pkg, opt_endrs to NULL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field1 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field2 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field3 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field4 = NULL"
DoCmd.RunSQL strSQL
'Get Filename
strFileName = GetFile("txt:*.txt", "Z:\mail\output\")
'Export (not working runtime error 3027 cannot update, read only)
DoCmd.TransferText acExportDelim, "Testy", FileNamePart(strFileName2),
strFileName, False
End Sub
It all works fine until the last step which is docmd.transfertext exporting a
variable table name
At the end when i try to export my file, i get a run time error 3027 message.
FileNamePart is a function that just extracts 'filename' out of C:\temp\
filename.txt
thanks!
Sub e()
'Set Variables
Dim strSQL As String
Dim strFileName As String
Dim strFileName2 As String
'Don't display a bunch of messages
DoCmd.SetWarnings False
'prompt for a filename to import
strFileName2 = GetFile("txt:*.txt", "z:\mail\output", "Import your
XXXXXall.txt file")
'import that file
DoCmd.TransferText acImportDelim, "All Import Specification",
FileNamePart(strFileName2), strFileName2, False
'Add an autonumber
DoCmd.RunSQL "ALTER Table " & (FileNamePart(strFileName2)) & " ADD
Column ID counter"
'Update SEQ field15
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field15 = id & ' / ' & field15"
DoCmd.RunSQL strSQL
'Update brk, ctn, pkg, opt_endrs to NULL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field1 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field2 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field3 = NULL"
DoCmd.RunSQL strSQL
strSQL = "UPDATE " & FileNamePart(strFileName2) & " SET " &
FileNamePart(strFileName2) & ".field4 = NULL"
DoCmd.RunSQL strSQL
'Get Filename
strFileName = GetFile("txt:*.txt", "Z:\mail\output\")
'Export (not working runtime error 3027 cannot update, read only)
DoCmd.TransferText acExportDelim, "Testy", FileNamePart(strFileName2),
strFileName, False
End Sub