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!!!

This is a sample of 1 of 3000, text files I have to import into Access. The data string always begins with S and subsequent lines in that data string begin with C. Every new line of data begins with S. The "S" and "C" are not part of the data only delimeters. The first 8 characters are part of a series of numbers that is a date i.e 1881121511A. format YYYYMMDD,=SurveyDate the next characters tell what kind of data elements it is "1" = Orginal Survey, 2= Retracement..etc this numeric value can change from 1-9, for various other kinds of surveys=SurveyKind. The next character "1", idenifies an angency 1= BLM, 2= NFS and so on 1-9 values=Agency. The alpha character at the end of the series of number indentifies the first survey "A" and second survey "B" etc.=SurveyID. I'm familiuar enough with parsing out values from a string into different field using 'mid(string,1,1)", but I know absolutly nothing about VB programming to get each of the element into the correct fields. Each of the subsequent strings are elements need to be places in single fields. .100 35000.0 35000. = Realiablity_Factors, GLO-UT= Source_Agnecy, SUBERT, F = Surveryor, 18820428 is another date "YYYYMMDD" = ApprovalDate , and SOUTH PORTION INTERIOR = Comments. I'm importing these text file into a table that already exists, "Twp_Sids.tlb"

This is the sample of the text files

S1881121511A .100 35000.0 35000.0
C GLO-UT
C SUBERT,F
C 18820428
C SOUTH PORTION INTERIOR
S1886081111A .100 35000.0 30000.0
C USGLO
C SALOMON,OE
C 18911031
C NORTH PORTION OF THE SUBDIVISION
S1886080531A .100 35000.0 35000.0
C USGLO
C SALOMON,OE
C 18911031
C INTERIOR LINES OF THE SUBDIVISION
S1894063031A .100100000.0100000.0
C USGLO
C GORLINSKI,R
C 18951123
C NORTH TWO MILES OF THE WEST BOUNDARY
S1884061631A .100 25000.0 25000.0
C USGLO
C KOEBER,EW
C 18840726
C SOUTH FOUR MILES OF THE WEST BOUNDARY
S1881083011A .100 25000.0 15000.0
C USGLO
C SUBERT,F
C 18820428
C EAST BOUNDARY

Any help will be appreciated

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open MM_ConCompare_STRING
objRecordSet.Open "SELECT * FROM ADInfo", objConnection, adOpenStatic, adLockOptimistic
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set folder = objFSO.GetFolder("C:\Inetpub\wwwroot\Compare\ADUploadedFiles\")
for each file in folder.Files
if lcase(objFSO.getExtensionName(file.path))="txt" then
Set testfile = objFSO.OpenTextFile(file.path, ForReading)
Do While Not testfile.AtEndOfStream
strImport = testfile.ReadLine
arrImport = Split(strImport, ",")
objRecordSet.AddNew
objRecordSet("test") = arrImport(0)
objRecordSet("firstname") = arrImport(1)
objRecordSet("surname") = arrImport(2)
objRecordSet("username") = arrImport(3)
objRecordSet("department") = arrImport(4)
objRecordSet("extension") = arrImport(5)
objRecordSet.Update
Loop
end if
testfile.close
next
objRecordSet.Close
objConnection.Close

I get the following error:

Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '[number: 1]'

Here : objRecordSet("firstname") = arrImport(1)

Any Ideas

Hi guys,

have a similar need, even little bit different. Let's see if you can help me, would be great because I'm in trouble with the following:

I have a Table in a DB
I have a text file to be imported inside this Table
I have the Import Specification rule scheme in order to make the Import in right way

Currently I do this manually, by importing external data, chose the text file, specify the Import Specification rules.

I Need to make this automated via VBA or Macro

Do you have a example code, or links to be checked?

Thanks in advance.
Edo

One of the suggestions posted should be adaptable to your situation. Darkosa utilized the Split function which has an argument to specify the delimiter character. This may be the best option for you.