ADO, pls help!

[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

[Eric White]

Could it be that the database is not being updated/refreshed with the new
records (e.g., using a batch update method)? Using your example, if you
upload the first record but the db is still in "edit" mode (the new records
are being held in a buffer, awaiting an update/refresh command to add them to
the db), the first duplicate record doesn't really exist in the database and
so Access happily adds both records to the db.

Assuming this is the case, having to update the db after every record is
loaded would be inefficient. What you'd probably want to do it to go ahead
and upload all the Excel data into a temporary table, then query it against
your existing Access data to find the duplicates, delete the duplicates from
the temporary table, find that record's row in Excel and mark it, and the
append the temporary table data to the existing Access table.

I'm just starting to be successful using ADO, so maybe the aforementioned
premise is incorrect and I'm just blowing smoke.

"Santiago" wrote:

> 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
>

[Santiago]

Thanks, but see the answer below to my first post, and you'll find the
solution!

bregards

"Eric White" wrote:

> Could it be that the database is not being updated/refreshed with the new
> records (e.g., using a batch update method)? Using your example, if you
> upload the first record but the db is still in "edit" mode (the new records
> are being held in a buffer, awaiting an update/refresh command to add them to
> the db), the first duplicate record doesn't really exist in the database and
> so Access happily adds both records to the db.
>
> Assuming this is the case, having to update the db after every record is
> loaded would be inefficient. What you'd probably want to do it to go ahead
> and upload all the Excel data into a temporary table, then query it against
> your existing Access data to find the duplicates, delete the duplicates from
> the temporary table, find that record's row in Excel and mark it, and the
> append the temporary table data to the existing Access table.
>
> I'm just starting to be successful using ADO, so maybe the aforementioned
> premise is incorrect and I'm just blowing smoke.
>
> "Santiago" wrote:
>
> > 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
> >

[Gareth]

I don't think your SQL code is updating the db on inserts correctly - as
Eric said. You haven't posted your SQL that inserts the data.

That said, I got it working fine, please see below code. (Watch for
wrapping...)

Note, I changed the recordset open line slightly.

Sub foo()

Dim cmd As ADODB.Command
Dim RECSET As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim iRow As Integer
Dim sSQLdupl As String
Dim sSQLvalues As String

Const myDBConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0"
Const myDBLocation As String = "C:\myDB.mdb"

'Open connection the database
Set cnn = New ADODB.Connection
With cnn
.Errors.Clear
.connectionString = myDBConnectionString
.Open myDBLocation
End With

Set RECSET = New ADODB.Recordset

iRow = 1
Do While Cells(iRow, 1) <> ""

sSQLdupl = "SELECT * FROM tblBASE"
sSQLdupl = sSQLdupl & " WHERE [ORDER] = '" & Cells(iRow, 2) & "'"
sSQLdupl = sSQLdupl & " AND [ITEM] = '" & Cells(iRow, 3) & "'"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = sSQLdupl
.CommandType = adCmdText
.Execute

End With

'I changed this line slightly
RECSET.Open sSQLdupl, cnn, , , 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
'RECSET.Close
sSQLvalues = "INSERT INTO tblBASE ([Order], [Item]) VALUES('" _
& Cells(iRow, 2) & "','" & Cells(iRow, 3) & "')"
cmd.CommandText = sSQLvalues
cmd.Execute

End If

iRow = iRow + 1
If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close

Loop
cnn.close
set cmd = nothing
set RECSET = nothing
set cnn = nothing
End Sub




Santiago wrote:
> Thanks, but see the answer below to my first post, and you'll find the
> solution!
>
> bregards
>
> "Eric White" wrote:
>
>
>>Could it be that the database is not being updated/refreshed with the new
>>records (e.g., using a batch update method)? Using your example, if you
>>upload the first record but the db is still in "edit" mode (the new records
>>are being held in a buffer, awaiting an update/refresh command to add them to
>>the db), the first duplicate record doesn't really exist in the database and
>>so Access happily adds both records to the db.
>>
>>Assuming this is the case, having to update the db after every record is
>>loaded would be inefficient. What you'd probably want to do it to go ahead
>>and upload all the Excel data into a temporary table, then query it against
>>your existing Access data to find the duplicates, delete the duplicates from
>>the temporary table, find that record's row in Excel and mark it, and the
>>append the temporary table data to the existing Access table.
>>
>>I'm just starting to be successful using ADO, so maybe the aforementioned
>>premise is incorrect and I'm just blowing smoke.
>>
>>"Santiago" wrote:
>>
>>
>>>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
>>>