Input Data from Text File - Checking for Data Already in DB

[RBohannon]

I'm using A2K.

I'm inputing data from a text file into my DB, and I need to check for
the data already existing in the DB. If it's already in the DB, I
don't want to reenter it.

The two tables being used are tblPersonal and tblListData.
tblPersonal contains names, SSNs, etc. SSN is the PrimaryKey.
tblListData is keyed on the combination of SSN and ExamNum. In
tblListData, an SSN can be paired with more than one ExamNum, but the
combination must be unique.

I need to do this:
If SSN is NOT in tblPersonal Then
add new record in tblPersonal
Else ' SSN is already in DB - check if SSN is already paired with
ExamNum
If the combinaton of SSN and ExamNum is NOT in tblListData Then
add new record in tblListData
End if
End If

The short version of my question is: How can I check programmaticall y
for the existence of the combinaton of SSN and ExamNum in tblListData?

Some details of what I have so far:
To do the first part (If SSN is NOT in tblPersonal then) I've written
a function using the Seek method:
Public Function Exists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

' strRS is the table beign searched
' strIndex is the index of the table
' strTarget is the value being searched for

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordse t(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
Exists = False
Else
Exists = True
End If

rs.Close

End Function

and used it like this:
' this code fragment is inside a while loop
' enter data into DB
' if SSN is not already in DB
If Not Exists("tblPers onal", "SSN", strSSN) Then
' create new record in tblPesonal
rsPersonal.AddN ew

rsPersonal!SSN = strSSN
rsPersonal!Elig Name = strName

[Squirrel]

Maybe try using DCount; it's used like this:

if DCount("[SSN]", "tblPersona l", "[SSN] = " & mySSN) = 0 then
.....
end if

if DCount("[SSN]", "tblListDat a", "[SSN] = " & mySSN & " and [ExamNum] = " &
myExamNum) = 0 then
.....
end if

Use single quotes if any of your data is char: e.g.
DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

HTH -Linda
"RBohannon" <ra*****@hotmai l.com> wrote in message
news:ad******** *************** ***@posting.goo gle.com...

I'm using A2K.

I'm inputing data from a text file into my DB, and I need to check for
the data already existing in the DB. If it's already in the DB, I
don't want to reenter it.

The two tables being used are tblPersonal and tblListData.
tblPersonal contains names, SSNs, etc. SSN is the PrimaryKey.
tblListData is keyed on the combination of SSN and ExamNum. In
tblListData, an SSN can be paired with more than one ExamNum, but the
combination must be unique.

I need to do this:
If SSN is NOT in tblPersonal Then
add new record in tblPersonal
Else ' SSN is already in DB - check if SSN is already paired with
ExamNum
If the combinaton of SSN and ExamNum is NOT in tblListData Then
add new record in tblListData
End if
End If

The short version of my question is: How can I check programmaticall y
for the existence of the combinaton of SSN and ExamNum in tblListData?

Some details of what I have so far:
To do the first part (If SSN is NOT in tblPersonal then) I've written
a function using the Seek method:
Public Function Exists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

' strRS is the table beign searched
' strIndex is the index of the table
' strTarget is the value being searched for

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordse t(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
Exists = False
Else
Exists = True
End If

rs.Close

End Function

and used it like this:
' this code fragment is inside a while loop
' enter data into DB
' if SSN is not already in DB
If Not Exists("tblPers onal", "SSN", strSSN) Then
' create new record in tblPesonal
rsPersonal.AddN ew

rsPersonal!SSN = strSSN
rsPersonal!Elig Name = strName
.
.
.
rsPersonal.Upda te
rsPersonal.Move Next

intCount = intCount + 1

But I don't know how I would check for the combinaton of SSN and
ExamNum in tblListData.

I apologize for the length of this post, and I hope I've been clear
enough.
Any help would be greatly appreciated.

[RBohannon]

Thanks for your help. I won't have a chance to try this until Monday, 8/23.

Randy

[Pieter Linden]

Of course, you could always append to an intermediate table and then
use an outer join to get the records that aren't already in the table
and import just those. All that with maybe two lines of code...

docmd.transferT ext...
docmd.OpenQuery ...

[Randy A. Bohannon]

An excellent idea. Thank you.

Of course, you could always append to an intermediate table and then
use an outer join to get the records that aren't already in the table
and import just those. All that with maybe two lines of code...

docmd.transfer Text...
docmd.OpenQuer y ...

[RBohannon]

I'm getting an error message that says "Error 3464: Data type mismatch
in criteria expression."

My code:
If DCount("[SSN]", "tblListDat a", "[SSN] = " & strSSN & " And
[ExamNum] = " & strExamNum) = 0 then

[RBohannon]

I got it.

I needed to enclose my criteria expression in single quotes:

If DCount("[SSN]", "tblListDat a", "[SSN] = '" & strSSN & " And
[ExamNum] = " & strExamNum & "'") = 0 Then

[RBohannon]

Well, it appeared to be working, but it was not. I have finally
figured it out. So, for the benefit of anyone else whom this may
help:
If the criteria is not literal, it must be enclosed in single quotes,
e.g:

If DCount("[SSN]", "tblListDat a", "[[SSN] = '" & strSSN & "' And
[ExamNum] = '" & strExamNum & "'") = 0 Then