String Manipulation and Access. 1

[brendalanigan]

Hello,

I dont normally do any VB programming so you will have to excuse my ignorance. I have been asked to extract all the individual lines of text from a text file and to take this information and put it into a table in an access database. I understand that this will involve looping through the text file to extract the information but I am unsure how I store the information then write each line of text extracted into a table in an access database.

Any help much appreciated.

 

[BobRodes]

You'll want to look at the following. FileSystemObject, TextStream, ReadLine, AtEndOfStream, ADO Connection Object, ADO Recordset Object. The first four are how to read from a text file. The last two are how to write to an Access Database.

Perhaps you'll do a bit of background reading and then get back with more specific questions.

HTH

Bob

 

[vbjock]

Here is a basic sample code you use and modify to your needs:
Private Sub Command1_Click()
' Need to Reference Microsoft ActiveX Data objects 2.1 or higher
Dim myRS As New ADODB.Recordset

Static ConnectStr As String

Dim dbPathName As String, sqlstr as string
dbPathName = "C:\MyAccess.mdb"

On Error GoTo Found_Error

Screen.MousePointer = vbHourglass

ConnectStr = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & dbPathName
'
sqlstr = "Select * from MyAccessTable"

myRS.Open sqlstr, ConnectStr, adOpenDynamic, adLockOptimistic

' The input text file has 7 items, comma delimited

Dim i1 As Variant, i2 As Variant, i3 As Variant, i4 As Variant
Dim i5 As Variant, I6 As String, i7 As Variant

Dim Filenum As Integer
Filenum = FreeFile
Open "C:\inputtextfile.txt" For Input As #Filenum
Dim kount As Integer
kount = 0

Do While Not EOF(Filenum)
Input #Filenum, i1, i2, i3, i4, i5, I6, i7

myRS.AddNew
kount = kount + 1
myRS.Fields(0) = kount
myRS.Fields(1) = i1
myRS.Fields(2) = i2
myRS.Fields(3) = ans
myRS.Fields(4) = i3
myRS.Fields(5) = i5
myRS.Fields(6) = i4
myRS.Fields(7) = I6
myRS.Fields(8) = i7
myRS.Update

Loop

myRS.Close
Set myRS = Nothing

Close #Filenum
Screen.MousePointer = vbDefault
MsgBox "Done - records processed: " & kount

Exit Sub

Found_Error:
Screen.MousePointer = vbDefault
MsgBox Err.Description & vbCr & "Err Num: " & Err.Number, vbCritical, "Error Encountered"

End Sub

 

[brendalanigan]

Thanks vbjock and BobRodes!

 

[JerryKlmns]

brendalanigan

You got VB answers but there is also an MS-Access one! Have you checked on msaccess File-->Get External Data-->Import?

 

[BobRodes]

Well. I wish I'd thought of that!

Brenda, that might be another way to go, especially if this is a one-off rather than a regularly recurring requirement.