Looking for two duplicates between Access and SQL Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My SQL table has two keys: Composite Name and Date. A user cannot add a
record if the name and date already exist in the database.

How should the code be written to query on two keys at the same time?

Set rst = db.OpenRecordset("SELECT * FROM dbo_Tbl_MonthlyData" & _
"WHERE Composite_Name = " & QUOTE & strCompositeName & QUOTE) & _
Month = " & QUOTE & varMonth & QUOTE)"

If strCompositeName and varMonth > 1 then

.... Msgbox: This record already exist....
 
Put a space between dbo_Tbl_MonthlyData and the WHERE statement, drop the
superflous right parenthesis and add the logical operator AND :

Set rst = db.OpenRecordset("SELECT * FROM dbo_Tbl_MonthlyData " & _
"WHERE Composite_Name = " & QUOTE & strCompositeName & QUOTE & _
" AND Month = " & QUOTE & varMonth & QUOTE)"

If the recordset is not at the EOF position after the opening (or if the
number of records is greater than 0), then you these datas are already in
the table.

Finally, you don't have provision in your code for the possibility that two
users will attempt the same test following by the same Insert at about the
same time.

S. L.
 
Back
Top