search a tablerow for a string

[Nirious]

So here is the problem, well, not actually a problem but rather a question to see if there isn't a better way to do this.

I have a textfile which contains lines of information. I read them line by line and than need to check wether or not they are already in the database. Therefor I need to compare a part of the string that I got out of the textfile-line to the database-table key. If they exist all is okey, if the key of the textfile isn't found in the database-table key column than it should be added (along with some other info)

1) So what would be the best way to check if a string is the same as a key in the keycolumn of the databasetable?

2) and another thing, can you give me an example of the EOF property used with a textreader. I seem to get a syntax error so I kinda solved it with the onerror thingy but I'd like to use the eof property.

This is my first proggy in vba so bare with me .

 

[Nirious]

Lol, I just thought about something.
If I use a adodb.command to do something in the likes of:

"SELECT CustID FROM Customers WHERE CustID = '" & mystring & "';" it should return how many records where affected right. So if it's one than its found, if it's zero than it should be created. Or maybe there's some other method.

Or maybe I can do it with handling some error that is thrown when a you try to append a duplicate key. But I have no idea how.
So lets make that a question

3)How do you know what error has been thrown?

 

[WayneRyan]

Nirious,

General idea ...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim buffer As String
Dim TheKey As String

Set dbs = CurrentDb

Open "C:\SomeFile.txt" For Input As #1
Set rst = dbs.OpenRecordset("YourTable")

Line Input #1, buffer
While Not EOF(1)
   TheKey = Mid(buffer, 15, 12)
   If DCount("[TheKey]", "YourTable", "[TheKey] = '" & TheKey & "'") > 0 Then
      ' Already there ... ignore
   Else
      rst.AddNew
      rst!TheKey = TheKey
      rst!SomeOtherField = Mid(buffer, 1, 7)
      rst.Update
   End If
   Line Input #1, buffer
   Wend
Set rst = Nothing
Set dbs = Nothing
Close #1

Wayne