[AccessD] Replace Function

I don't understand y it crashes. I thought about creating a macro in CSV
file that would convert format from 19871002 to 10021987, and then
transfer data to Access to throug access original transfer fields, but
that gets in the way of fiels such as "Mgr, International" that use coma.

This program seems to solve coma proble, but not apostraphe problem :--)

Private Sub Command14_Click()

 MsgBox ("hey")
 Dim fs As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim strLine As String
Dim aRecord
Dim strSQL As String
Dim db As DAO.Database
Dim i As Integer

Set db = CurrentDb


If fs.FileExists("U:\Users\HRIS\oleg\cars testing\trainFROM.csv") Then
    Set ts = fs.OpenTextFile("U:\Users\HRIS\oleg\cars
testing\trainFROM.csv", ForReading)

    Do Until ts.AtEndOfStream

        strLine = ts.ReadLine
       'strLine = Replace(strLine, """, " ") 'need to replace " with space
       strLine = Replace(strLine, ",", vbTab)

        aRecord = Split(strLine, vbTab)
        aRecord(8) = Mid(aRecord(8), 5) & Left(aRecord(8), 4)


        'strSQL = "INSERT into Table1Testing(a, b, c,d,e) Values(" & "'" &
aRecord(0) & "','" & aRecord(1) & "','" & Replace01(aRecord(2),
"'", "''") & "','" & Replace01(aRecord(3), "'", "''") & "','" &
Replace01(aRecord(4), "'", "''") & "')"
       'strSQL = "INSERT into Table1Testing(a, b, c,d,e) Values(" & "'" &
aRecord(0) & "','" & aRecord(1) & "','" & Replace(aRecord(2),
Chr(39), Chr(39) + Chr(39)) & "','" & Replace(aRecord(3), Chr(39),
Chr(39) + Chr(39)) & "','" & Replace(aRecord(4), Chr(39), Chr(39) +
Chr(39)) & "')"
        strSQL = "INSERT into Table1Testing(a, b, c,d,e) Values(" & "'" &
aRecord(0) & "','" & aRecord(1) & "','" &
Replace(CStr(aRecord(2)), Chr(39), Chr(39) + Chr(39)) & "','" &
Replace(CStr(aRecord(3)), Chr(39), Chr(39) + Chr(39)) & "','" &
Replace(CStr(aRecord(4)), Chr(39), Chr(39) + Chr(39)) & "')"

        Debug.Print strSQL
        CurrentDb.Execute (strSQL)

    Loop
Else
     MsgBox ("file doesn't exist")
End If
    MsgBox ("end")
End Sub
-----------------------------------------------------------



> Hi Oleg
>
> And what do the other records look like?
>
> /gustav
>
>> I don't think this will ever. Ok it doesn't give my ByRef error
>> anymore, it just crashes every time around the record 144 (??) All
>> other transfer forms work fine exsept in this in the original someone
>> jut had to put date in format 19860102....
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail