- The code works fine if there is no missing value. I have tested on one record and multiple records, the code runs fine.
- Please note that I write FieldD and FieldF differently, because these two fields are string, the remaining five fields are integer.
- 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.
- 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.
I need to add some space in SQL code, in order to post the code.
LastRow = WB.Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row
Cn.Open "Sybase Database Connection String"
For i = 2 To LastRow
AddRecordQuery = "I N S E RT I N T O Table_name (FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG) V A LUES (" _
& 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.
Bookmarks