Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 25th, 2005, 10:31 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default data from txt files to access db

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 ?
Reply With Quote
  #2 (permalink)  
Old January 25th, 2005, 12:18 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old January 25th, 2005, 12:21 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old January 25th, 2005, 12:50 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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'...
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
WHERE IS THE TXT FILES OF BOOK? PedroPN BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 2 July 5th, 2007 10:32 AM
Problem with inserting data into an Access DB donrafeal ASP.NET 2.0 Basics 0 January 6th, 2006 05:34 PM
Uploading txt files arnabghosh Classic ASP Basics 1 June 15th, 2005 04:44 PM
Output my txt from DB using JSP Befekadu Pro JSP 3 April 29th, 2005 06:48 AM
Working with .txt files ArtDecade VB How-To 2 September 10th, 2004 10:05 PM



All times are GMT -4. The time now is 12:49 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.