[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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.
0
jwellis
Asked:
jwellis
  • 3
  • 3
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now