dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Text file update Access Table

  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Question Text file update Access Table

    I need some programming help with a problem. I have a delimited text file that have to update all records in an Access table. Hereís what Iím looking for: The Text file is an employee data file with updates or changes in status, address, salary etc., for all employees (7,000 records in the file). I need a code written to change the Access employee Table with the information found in the Text file. The unique identifier is the SSN#. How can I write a code that will take the information from the Text file to change or update or replace the employee Table file? Plus I need it to put in new employees and delete employees that not found in the Text file but is in the Table file.
    Please help. The last time I written a code it was in COBOL.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    You can start off by opening the text file using ADO. Since it sounds like there are several different types of updates, you will probably need to read the data in line by line and then determine what type of operation you need to perform on the corresponding data in the Access database.

    I don't whether the text file has a header with column names, but if not the default column names will be F1, F2, F3, etc.

    Code:
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    'Open folder where text file is located
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text;" & _
                "Extended Properties=""Text;HDR=No;"""
    
    Dim strSQL As String
    
    strSQL = "SELECT * FROM Employees#txt"
    
    'Open text file
    rs.Open strSQL, cnn
    
    While Not rs.EOF
    
         'Perform lookups on Access database table using SSN (rs.Fields("F1").Value if first column)
         'Do updates, inserts
         '...
         '...
    
         rs.MoveNext
    
    Wend
    
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jan 2007
    Posts
    2

    Text to Access

    Thanks I'll give it a try. You've been more than helpful to me. At least I have a place to start. Thanks again

Similar Threads

  1. Script for scrolling
    By Mark in forum Web
    Replies: 3
    Last Post: 08-30-2001, 11:45 AM
  2. NullPointerException when reading text file
    By Andrew McLellan in forum Java
    Replies: 3
    Last Post: 05-09-2001, 05:34 PM
  3. Replies: 0
    Last Post: 08-22-2000, 10:54 PM
  4. Replies: 0
    Last Post: 04-17-2000, 01:33 PM
  5. Replies: 0
    Last Post: 04-17-2000, 01:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center