I create db and table and now. I need help with to copy data from (*.txt; *.csv) file to access database..

Private Sub Smth_Click()

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

    Debug.Print conn.ConnectionString
     conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
     "DBQ=c:\cos\;"
    
     rst.Open "select * from [data#txt]", conn, adOpenStatic, _
     adLockReadOnly

       Do Until rst.EOF
            For Each fld In rst.Fields
                fld.Name & "=" & fld.Value
            Next fld
          rst.MoveNext
          Loop
           conn.Close
           Set conn = Nothing


     ....

data.txt is like:

Mike;Kane;10
Stephanie;Moore;12
John;Nowak;16

how to copy this data to db ?

This is how I do it on an On Click event. I modified it to parse your data: It assumes you have created a DSN for your Access file.

'===Code Starts===
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists("C:\data.txt") Then
    Set ObjStream = FSO.OpenTextFile("C:\data.txt", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine

        New Array = Split(strLine, ";")
            strFirstName = NewArray(0)
            strLastName = NewArray(1)
            intNumber = NewArray(2)

    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=YourDataBaseDSNName;"

    objRS.Open "SELECT * FROM tblYourTableName", objConn, 3, 3
        objRS.AddNew
        objRS("FirstName") = strFirstName
        objRS("LastName") = strLastName
        objRS("Number") = intNumber
        objRS.Update
    objRS.Close
    objConn.Close

Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("c:\data.txt", True)

'===Code Ends===

HTH


mmcdonal

I should mention that the last part overwrites the data.txt file to clear it. I get my data from another process, so I need to do this. You will need to do this to prevent duplicate data.

mmcdonal

Private Sub Polecenie8_Click()

Dim fso As Object
Dim objStream As Object
Dim strLine As Object
Dim NewArray

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Dim strFirstName, strLastName, intNumber As String

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\cos\data.txt") Then

' nazwa pliku, 1 = do odczytu, False=utwozyc jesli nie istnieje, Format - okreœla format otwartego pliku; wartoœæ domyœlna to plik ASCII; parametr opcjonalny

    Set objStream = fso.OpenTextFile("C:\cos\data.txt", 1, False, 0)
End If

' AtEndOfStream - zwraca wartoœæ TRUE jeœli wskaŸnik pliku znajduje siê na koñcu pliku

Do While Not objStream.AtEndOfStream

' ReadLine - odczytuje ca³¹ liniê z obiektu TextStream i zwraca j¹ w postaci ³añcucha bez znaku koñca linii

    strLine = objStream.ReadLine

        NewArray = Split(strLine, ";")
            strFirstName = NewArray(0)
            strLastName = NewArray(1)
            intNumber = NewArray(2)

    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "Nowa.dba;"

    objRS.Open "SELECT * FROM Studenci", objConn, 3, 3

        objRS.AddNew
        objRS("imie") = strFirstName
        objRS("nazwisko") = strLastName
        objRS("nr_indeksu") = intNumber
        objRS.Update
    
Loop

    objRS.Close
    objConn.Close

Set fso = CreateObject("Scripting.FileSystemObject")
'Set MyFile = fso.CreateTextFile("c:\cos\data.txt", True)


End Sub

Get some errors here:

Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine

'Object Variable or with block not set'...