S
Santiago
I posted this msg below, but I really need an answer... I appreciate if
anyone can help me out.
/quote
Hi guys, I'm having some problems with the following code. The idea is to
upload data to an Access database (using ADO) from an Excel Sheet, but
checking first if there already exists a record with the same [order] and
[item] numbers. If it already exists should paint the row in yellow and add a
comment.
Here's the problem and the code.
PROBLEM: to try this code, I put 2 rows in the sheet with the same order and
item that do not previously exist in the database. So first row should be
uploaded and second should be painted in yellow with a comment "NOT ADDED".
It doesn't do it, it uploads both records. But, if I put in the first row
[order] [item] numbers that already exist in the dbase, it works perfectly...
CODE:
Set Command = New ADODB.Command
Command.ActiveConnection = connectionString1
Set RECSET = New ADODB.Recordset
Do While Cells(iRow, 1) <> ""
sSQLdupl = "SELECT * FROM [tblBASE]"
sSQLdupl = sSQLdupl & " WHERE [ORDER] = " & Cells(iRow, 2)
sSQLdupl = sSQLdupl & " AND [ITEM] = " & Cells(iRow, 3)
Call RECSET.Open(sSQLdupl, connectionString, , ,
CommandTypeEnum.adCmdText)
If Not RECSET.EOF Then
Cells(iRow, 1).ClearComments
Cells(iRow, 1).AddComment
Cells(iRow, 1).Comment.Visible = False
Cells(iRow, 1).Comment.Text Text:="ROW NOT ADDED"
Range(iRow & ":" & iRow).Interior.ColorIndex = 6
Else
sSQLvalues = "INSERT INTO tblBASE " & 'all the fields I want to
upload...
" VALUES ('" & 'all the values I want to upload
Command.CommandText = sSQLvalues
Call Command.Execute(, , CommandTypeEnum.adCmdText)
End If
iRow = iRow + 1
If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Loop
Thanks for the help. Bregards
Santiago
anyone can help me out.
/quote
Hi guys, I'm having some problems with the following code. The idea is to
upload data to an Access database (using ADO) from an Excel Sheet, but
checking first if there already exists a record with the same [order] and
[item] numbers. If it already exists should paint the row in yellow and add a
comment.
Here's the problem and the code.
PROBLEM: to try this code, I put 2 rows in the sheet with the same order and
item that do not previously exist in the database. So first row should be
uploaded and second should be painted in yellow with a comment "NOT ADDED".
It doesn't do it, it uploads both records. But, if I put in the first row
[order] [item] numbers that already exist in the dbase, it works perfectly...
CODE:
Set Command = New ADODB.Command
Command.ActiveConnection = connectionString1
Set RECSET = New ADODB.Recordset
Do While Cells(iRow, 1) <> ""
sSQLdupl = "SELECT * FROM [tblBASE]"
sSQLdupl = sSQLdupl & " WHERE [ORDER] = " & Cells(iRow, 2)
sSQLdupl = sSQLdupl & " AND [ITEM] = " & Cells(iRow, 3)
Call RECSET.Open(sSQLdupl, connectionString, , ,
CommandTypeEnum.adCmdText)
If Not RECSET.EOF Then
Cells(iRow, 1).ClearComments
Cells(iRow, 1).AddComment
Cells(iRow, 1).Comment.Visible = False
Cells(iRow, 1).Comment.Text Text:="ROW NOT ADDED"
Range(iRow & ":" & iRow).Interior.ColorIndex = 6
Else
sSQLvalues = "INSERT INTO tblBASE " & 'all the fields I want to
upload...
" VALUES ('" & 'all the values I want to upload
Command.CommandText = sSQLvalues
Call Command.Execute(, , CommandTypeEnum.adCmdText)
End If
iRow = iRow + 1
If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Loop
Thanks for the help. Bregards
Santiago