Upload a txt file from through ACCESS 97 from VB 6.0

I would like to be able to upload a txt file from VB, into ACCESS 97.  What's the trick.  Please help.  Thanks.
jwellisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

halapayaCommented:
Why can't you write what ever in the text file, to a access data field which datatype is of Memo ??

no specail tricks.. same way as u write any other data[text] to the database
0
jwellisAuthor Commented:
That sounds simple enought.  But, I'm a littl new at this stuff and need an example of what you are talking about.  When the file is uploaded to ACCESS, it needs to sort data into different tables and then delete duplicates within each table.
0
RuchiCommented:
Use DAO ... Add Microsoft DAO 3.5 Object Library.  You can use different Data types and you need to convert them before assigning them to the fields.

If you have VB6

Dim aFields As Variant
Dim sRecord As String
Dim iField As Integer
Dim oDB As Database
Dim oRS As Recordset
Set oDB = WorkSpaces(0).OpenDatabase("C:\MyDB.mdb")
Set oRS = oDB.OpenRecordSet("SELECT * FROM MyTable", dbOpenDynaset)
Open "C:\FlatFile.dat" For Input As 1
While Not EOF(1)
    Line Input #1, sRecord
    'Assuming a Comma Delimiter
    aFields = Split(sRecord, ",")
    oRS.AddNew
    For iField = 0 To UBound(aFields)
        oRS(iField) = aFields(iField)
    Next
    oRS.Update
Wend




If you have VB5 or earlier,

Dim aFields As Variant
Dim sRecord As String
Dim iField As Integer
Dim oDB As Database
Dim oRS As Recordset
Set oDB = WorkSpaces(0).OpenDatabase("C:\MyDB.mdb")
Set oRS = oDB.OpenRecordSet("SELECT * FROM MyTable", dbOpenDynaset)
Open "C:\FlatFile.dat" For Input As 1
While Not EOF(1)
    Line Input #1, sRecord
    'Assuming a Comma Delimiter
    aFields = SplitString(sRecord, ",")
    oRS.AddNew
    For iField = 0 To UBound(aFields)
        oRS(iField) = aFields(iField)
    Next
    oRS.Update
Wend

Public Function SplitString(ByVal sString As String, ByVal sSeperator As String) As Variant
Dim sStrings() As String
Dim iStrings As Integer
Dim iPos As Integer

If sString = "" Then Exit Function
iPos = 1
Do
    ReDim Preserve sStrings(iStrings)
    iPos = InStr(sString, sSeperator)
    sStrings(iStrings) = sString
    If iPos Then sStrings(iStrings) = Left(sString, iPos)
    sString = IIf(iPos, Mid(sString, iPos + 1), "")
    iStrings = iStrings + 1
Loop While Len(sString) > 0

SplitString = sStrings
End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jwellisAuthor Commented:
Ruchi, that looks like it might work but one thing, my data is not have comma delimiters.  Is there anything I need to different since this is the case.
   
0
RuchiCommented:
Ok, you can change the type data to whatever..

aFields = SplitString(sRecord, "whatever")


0
RuchiCommented:
Another one ...

Place three text boxes and a combobox and a command button on the form.


Option Explicit
Private Sub cmdImport_Click()
Dim delimiter As String
Dim wks As Workspace
Dim db As Database
Dim fnum As Integer
Dim text_line As String
Dim sql_statement As String
Dim pos As Integer
Dim num_records As Long

    delimiter = cboDelimiter.Text
    If Len(delimiter) = 0 Then
        MsgBox "Please select a delimiter"
        Exit Sub
    End If

    If delimiter = "<space>" Then delimiter = " "
    If delimiter = "<tab>" Then delimiter = vbTab

    ' Open the text file.
    fnum = FreeFile
    On Error GoTo NoTextFile
    Open txtTextFile.Text For Input As fnum

    ' Open the database.
    On Error GoTo NoDatabase
    Set wks = DBEngine.Workspaces(0)
    Set db = wks.OpenDatabase(txtDatabaseFile.Text)
    On Error GoTo 0

    ' Read the file and create records.
    Do While Not EOF(fnum)
        ' Read a text line.
        Line Input #fnum, text_line
        If Len(text_line) > 0 Then
            ' Build an INSERT statement.
            sql_statement = "INSERT INTO " & _
                txtTable.Text & " VALUES ("
            Do While Len(text_line) > 0
                pos = InStr(text_line, delimiter)
                If pos = 0 Then
                    ' Add the rest of the line.
                    sql_statement = sql_statement & _
                        "'" & text_line & "', "
                    text_line = ""
                Else
                    ' Add the next field.
                    sql_statement = sql_statement & _
                        "'" & Left$(text_line, pos - 1) & _
                        "', "
                    text_line = Mid$(text_line, pos + Len(delimiter))
                End If
            Loop

            ' Remove the last comma.
            sql_statement = Left$(sql_statement, Len(sql_statement) - 2) & ")"

            ' Insert the record.
            On Error GoTo SQLError
            db.Execute sql_statement
            On Error GoTo 0
            num_records = num_records + 1
        End If
    Loop

    ' Close the file and database.
    Close fnum
    db.Close
    wks.Close
    MsgBox "Inserted " & Format$(num_records) & " records"
    Exit Sub

NoTextFile:
    MsgBox "Error opening text file."
    Exit Sub

NoDatabase:
    MsgBox "Error opening database."
    Close fnum
    Exit Sub

SQLError:
    MsgBox "Error executing SQL statement '" & _
        sql_statement & "'"
    Close fnum
    db.Close
    wks.Close
    Exit Sub
End Sub
Private Sub Form_Load()
    ' Enter default file and database names.
    txtTextFile.Text = App.Path & "\testdata.txt"
    txtDatabaseFile.Text = App.Path & "\testdata.mdb"
End Sub


0
jwellisAuthor Commented:
Thanks for the help Ruchi.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.