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.
Code:
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
