Text file update Access Table

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