Importing Text into Access Table via VBA

I am relatively new to coding in VBA and am trying to import a text file into a table in Access 2000 using VBA. I am able to open the text file and get the data I need, but am having diffucilty getting the text strings into the table. When I run this code, I get error message "Variable Not Defined" and it stops on the line of code "tblTextFile.AcctNumber = timpAN". I do not know if I am not opening the table correctly or how to "activate" the table to insert the new record to handle the new data. Here is the code I have so far...any help would be greatly appreciated

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

End Function

Is this will be on going process for you of it just one time thing.

If it's one time

you can just import data from text file into Access 2000 without coding.

This is just the beginning process of creating multiple import routines. Most of the other text files that I will be importing are not in a perfect delimited or fixed width format...so I will need code to extract the exact data I need and then get that data into a database. Any help would be apprecitated. Thanks.

I took your program and modifed it for my purposes. You can look at the
program to see how I added the data to the tables. I used the subroutine,
and it added the data as expected.I hope this is helpful.

Don

Sub ImportTextFile()
Dim LineData As String
Dim ICDraw As String ' Holder for Account Number in text file
Dim ICDDesc As String ' Holder for the Cusip Number or Ticker Symbol
Set cncurrent = CurrentProject.Connection
Set rsDiag = New ADODB.Recordset
' Open the text file
Open "F:\Documents and Settings\DFinnie\Desktop\v21icd9_diag.txt" For Input As #1
' Open the table to insert the text file into
strsql = "Select * from tblICD9"
rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
ICDraw = Trim(Left(LineData, 6))
ICDDesc = Trim(Mid(LineData, 7))

rsDiag.AddNew
rsDiag!ICD9raw = ICDraw
rsDiag!Description = ICDDesc
rsDiag.Update

Loop
' Close the data file.
Close #1
rsDiag.Close
End Sub

Thank You!!!