SQL: How to insert record into database table when there is missing value?

[VAer]

It is about SQL code in Excel VBA. Maybe people in this forum knows SQL better, so I post it here.

1. The VBA code works fine if there is no missing value. I have tested on one record and multiple records, the code runs fine.
2. Please note that I write FieldD and FieldF differently, because these two fields are string, the remaining five fields are integer.
3. I tested the code on a file with one single record, and if only FieldF is missing, the code runs fine and is able to insert the other 6 fields into database table.
4. I tested the code on a file with one single record, and if only FieldE is missing, the code does not run, see the attached error message.

Column A will never be missing, so it is fine for the code to check LastRow.

LastRow = WB.Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row

Cn.Open "Sybase Database Connection String"
    
    For i = 2 To LastRow    
       
           
        AddRecordQuery = "INSERT    INTO Table_name (FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG)  VALUES (" _
        & WB.Sheets("Test").Cells(i, 1).Value & ", " _
        & WB.Sheets("Test").Cells(i, 2).Value & ", " _
        & WB.Sheets("Test").Cells(i, 3).Value & ", " _
        & Chr(39) & WB.Sheets("Test").Cells(i, 4).Value & Chr(39) & ", " _
        & WB.Sheets("Test").Cells(i, 5).Value & ", " _
        & Chr(39) & WB.Sheets("Test").Cells(i, 6).Value & Chr(39) & ", " _
        & WB.Sheets("Test").Cells(i, 7).Value & ")"
        
        Set TestRs = CreateObject("ADODB.Recordset")
        
        TestRs.Open AddRecordQuery, Cn
        
        Set TestRs = Nothing
    
    
    Next i

Another question: how to test if a record is already in the database table? The database system will generate error message if the identical record is already in the database table, how should I use VBA to check it then I can skip adding that record.

Thanks.

[Bulzie]

Is FieldE part of the Key or does it have an index on it or an option that says it cannot be null?

On 2nd questions, in Access you can do a DLookup statement to test for a record in a table.

[Micron]

1) Further to Bulzie's question, if the target table allows Null or zero length strings, use the Nz function to coerce the missing data to either. You should be able to choose "" or Null as a valid substitute.
2) you trap for the error using a message handler and resume with the next line in sequence or do a lookup as suggested, or run a query and check for a recordset count. Which is better would depend on variables such as is the table native to the db or is it ODBC? Are you going across a network? Could there be one lookup or 1,000? etc.