How to import Text into Access through VBA

[appueye]

Hi friends,

I have written a code in VBA ,where i am trying to import a text file into a table in Access 2000 using VBA.Everything is working fine but when i try to open ant string and get data at that time i am getting an error message as "Variable Not Defined".

Can anyone fix this issue for me.

[Aarpitt]

Sure i will help you out with this i would really appreciate if you can provide me the code where i can find out where you have made mistake.

[appueye]

Here is the code

Function ImportTextFile()
Dim LineData As String
Dim timpAN As String ' Holder for Account Number in text file
Dim timpCT As String ' Holder for the Cusip Number or Ticker Symbol
Dim nimpSH As Double ' Holder for the number of shares held in account
Dim timpLS As String ' Holder for Long or Short Position
Dim nimpVAL As Double ' Holder for value of security
Dim nimpNAV As Double ' Holder for Net Asset Value

' Open the text file
Open "C:\Documents and Settings\User\Desktop\CS060104.txt" For Input As #1
' Open the table to insert the text file into
DoCmd.OpenTable "tblTextFile", acNormal, acEdit

Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData

timpAN = Left(LineData, 8)
tblTextFile.AcctNumber = timpAN

timpCT = Mid(LineData, 10, 9)
nimpSH = Mid(LineData, 20, 14)
timpLS = Mid(LineData, 35, 1)
nimpVAL = Mid(LineData, 37, 14)
nimpNAV = Mid(LineData, 52, 9)
Loop

' Close the data file.
Close #1

and it gets stops a on the line of code "tblTextFile.AcctNumber = timpAN"