Text file update Access Table

[charligov]

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.

[pclement]

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.

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

[charligov]

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