Transfertext not working need spec file

[vba317]

After researching the issue I have tried to use vba code to import a text file into my database with no success. It looked like a straight forward doCmd. but I am getting errors. I can do a manual import with a spec file which I created. If I don't use the spec file I get all kinds of incorrect data into the table. So I guess my first question is how do I get this spec file? The name of the spec file is CS_Data_Import_Spec. I have highlighted in Yellow the line that causes the error. I am getting error 3625 . The text file specification 'Filename' does not exist. Thanks for any help provided.

Option Compare Database

Const iFMonthStart = 10   ' Numeric value representing the first month
                              ' of the fiscal year.
Const iFDayStart = 1    ' Numeric value representing the first day of
                              ' the fiscal year.
Const iFYearOffset = -1   ' 0 means the fiscal year starts in the
                              ' current calendar year.
                              ' -1 means the fiscal year starts in the
                              ' next calendar year.
                              '1 means the fiscal year starts in previous calendar year

     Function GetFiscalYear(ByVal x As Variant)
         If x < DateSerial(Year(x), iFMonthStart, iFDayStart) Then
            GetFiscalYear = Year(x) - iFYearOffset - 1
         Else
            GetFiscalYear = Year(x) - iFYearOffset
         End If
      End Function

      Function GetFiscalMonth(ByVal x As Variant)
         Dim m
         m = Month(x) - iFMonthStart + 1
         If Day(x) < iFDayStart Then m = m - 1
         If m < 1 Then m = m + 12
         GetFiscalMonth = m
      End Function

Public Function CurRptMon(ipd As Integer, strCurMonL As String, strCurMonS As String, iCurFiscalMon As Integer, iCurFiscalYr As Integer, iCurCalYr As Integer, iCurRptMon As Integer, dtCurDate As Date) As Integer
Dim dtCurMon As Date
Dim iFiscalMon As Integer
Dim iPdCalc As Integer

dtCurDate = Date
iFiscalMon = iFMonthStart
iCurFiscalMon = GetFiscalMonth(dtCurDate) - 1
iCurFiscalYr = GetFiscalYear(dtCurDate)
iCurCalYr = Format(dtCurDate, "yyyy")
iCurRptMon = Month(DateAdd("m", -1, Date))
strCurMonL = MonthName(iFiscalMon, False)
strCurMonS = MonthName(iFiscalMon, True)
iPdCalc = 369
ipd = iPdCalc + iCurFiscalMon
End Function


'ImportFile Function
Option Compare Database
Public Function ImportFile()
Dim strFileLoc As String
Dim strTbl1Name As String
Dim strTbl2Name As String
Dim strFileExt As String
Dim strCurRptMon As String
Dim strCurMon As String
Dim ipd As Integer
Dim strCurMonL As String
Dim strCurMonS As String
Dim iCurFiscalMon As Integer
Dim iCurFiscalYr As Integer
Dim iFiscalMon As Integer
Dim iCurCalYr As Integer
Dim iCurRptMon As Integer
Dim dtCurDate As Date
Dim strSpecName As String
strCurRptMon = CurRptMon(ipd, strCurMonL, strCurMonS, iCurFiscalMon, iCurFiscalYr, iCurCalYr, iCurRptMon, dtCurDate)
strFileLoc = "M:\"
strFileExt = ".txt"
strTbl1Name = "NBM_CS_Data_" & iCurRptMon & iCurCalYr & "_" & iCurRptMon & iCurCalYr
'Name of file strTbl1Name = "NBM_CS_Data_102012_102012"
[Yellow]DoCmd.TransferText TransferType:=acImportDelim, TableName:=strTbl1Name, FileName:=strFileLoc & strTbl1Name, HasFieldNames:=True [/Yellow]
End Function

 

[PHV]

DoCmd.TransferText TransferType:=acImportDelim, TableName:=strTbl1Name, FileName:=strFileLoc & strTbl1Name, HasFieldNames:=True[!], SpecificationName:="CS_Data_Import_Spec"[/!]

Furthermore, I guess you should use this ?
FileName:=strFileLoc & strTbl1Name[!] & strFileExt[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV I have tried all your possibilities with no luck and tried three different others all with errors.
Currently I am getting an error 31519 You cannot import this file

strCurRptMon = CurRptMon(ipd, strCurMonL, strCurMonS, iCurFiscalMon, iCurFiscalYr, iCurCalYr, iCurRptMon, dtCurDate)
strPath = "M:\"
strFileExt = ".txt"
strSpecName = "CS_Data_Import_Spec"
strTbl1Name = "NBM_CS_Data_" & iCurRptMon & iCurCalYr & "_" & iCurRptMon & iCurCalYr & strFileExt

'Name of file strTbl1Name = "NBM_CS_Data_102012_102012"
'DoCmd.TransferText ([TransferType:=acImportDelim, SpecificationName:="CS_Data_Import_Spec", TableName:="NBM_CS_Data_102012_102012",FileName:="M:\NBM_CS_Data_102012_102012.txt", HasFieldNames:=True])
'DoCmd.TransferText acImportDelim, "CS_Data_Import_Spec", "NBM_CS_Data_102012_102012", "M:\NBM_CS_Data_102012_102012.txt", HasFieldNames:=True
DoCmd.TransferText acImportFixed, strSpecName, strTbl1Name, strPath, True


'DoCmd.TransferText ([TransferType:=acImportDelim, SpecificationName:="CS_Data_Import_Spec", 'TableName:="NBM_CS_Data_102012_102012",FileName:="M:\NBM_CS_Data_102012_102012.txt", HasFieldNames:=True])
'DoCmd.TransferText acImportDelim, "CS_Data_Import_Spec", "NBM_CS_Data_102012_102012", "M:\NBM_CS_Data_102012_102012.txt", HasFieldNames:=True

DoCmd.TransferText acImportFixed, strSpecName, strTbl1Name, strPath, True