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

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 programmatically
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.OpenRecordset(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("tblPersonal", "SSN", strSSN) Then
' create new record in tblPesonal
rsPersonal.AddNew

rsPersonal!SSN = strSSN
rsPersonal!EligName = strName

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

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

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

if DCount("[SSN]", "tblListData", "[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" <ranb550@hotmail.com> wrote in message
news:ad618ae4.0408130845.65e7577f@posting.google.c om...[color=blue]
> 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 programmatically
> 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.OpenRecordset(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("tblPersonal", "SSN", strSSN) Then
> ' create new record in tblPesonal
> rsPersonal.AddNew
>
> rsPersonal!SSN = strSSN
> rsPersonal!EligName = strName
> .
> .
> .
> rsPersonal.Update
> rsPersonal.MoveNext
>
> 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.[/color]

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

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

Randy

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

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.transferText...
docmd.OpenQuery ...

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

An excellent idea. Thank you.
[color=blue]
>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.transferText...
>docmd.OpenQuery ...[/color]

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

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

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

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

I got it.

I needed to enclose my criteria expression in single quotes:

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

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

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]", "tblListData", "[[SSN] = '" & strSSN & "' And
[ExamNum] = '" & strExamNum & "'") = 0 Then