ADO & Loops: error in code?

S

Santiago

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
 
D

Dick Kusleika

Santiago:

It worked okay for me. I don't use the Command object, but use the
Connection object instead. I can't believe that makes a difference though.
When you step through the code, is the recordset actually empty after you
execute the INSERT INTO? I would set up another recordset variable and stop
execution after the INSERT INTO and play around with that variable to see if
I could get the newly inserted record to show up. Here's the code I used:

Sub WriteRecords()

Dim lRow As Long
Dim rstOrders As ADODB.Recordset
Dim conOrders As ADODB.Connection
Dim sConn As String
Dim sSQL As String, sWhere As String
Dim sInsert As String

lRow = 1
sConn = "DSN=MS Access 97 Database;" & _
"DBQ=C:\Documents and Settings\Dick\My Documents\db3.mdb;" & _
"DefaultDir=C:\Documents and Settings\Dick\My Documents;" & _
"DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSQL = "SELECT * FROM tblOrders WHERE "

Set conOrders = New ADODB.Connection
conOrders.Open sConn

Set rstOrders = New ADODB.Recordset

With Sheet1
Do While Not IsEmpty(.Cells(lRow, 1).Value)
sWhere = "(order='" & .Cells(lRow, 1).Value & _
"') AND (item='" & .Cells(lRow, 2).Value & "')"

rstOrders.Open sSQL & sWhere, conOrders

If Not rstOrders.EOF Then
Debug.Print "dup on row " & lRow
Else
sInsert = "INSERT INTO tblOrders ([order], item) VALUES('" &
_
.Cells(lRow, 1).Value & "','" & .Cells(lRow, 2).Value &
"')"

conOrders.Execute sInsert
End If

lRow = lRow + 1

rstOrders.Close

Loop
End With

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com
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
Click to expand...
 
S

Santiago

Thanks, worked fine. But still don't get it why didn't work.

THANKS!!!!!!!!!!!

Dick Kusleika said:
Santiago:

It worked okay for me. I don't use the Command object, but use the
Connection object instead. I can't believe that makes a difference though.
When you step through the code, is the recordset actually empty after you
execute the INSERT INTO? I would set up another recordset variable and stop
execution after the INSERT INTO and play around with that variable to see if
I could get the newly inserted record to show up. Here's the code I used:

Sub WriteRecords()

Dim lRow As Long
Dim rstOrders As ADODB.Recordset
Dim conOrders As ADODB.Connection
Dim sConn As String
Dim sSQL As String, sWhere As String
Dim sInsert As String

lRow = 1
sConn = "DSN=MS Access 97 Database;" & _
"DBQ=C:\Documents and Settings\Dick\My Documents\db3.mdb;" & _
"DefaultDir=C:\Documents and Settings\Dick\My Documents;" & _
"DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSQL = "SELECT * FROM tblOrders WHERE "

Set conOrders = New ADODB.Connection
conOrders.Open sConn

Set rstOrders = New ADODB.Recordset

With Sheet1
Do While Not IsEmpty(.Cells(lRow, 1).Value)
sWhere = "(order='" & .Cells(lRow, 1).Value & _
"') AND (item='" & .Cells(lRow, 2).Value & "')"

rstOrders.Open sSQL & sWhere, conOrders

If Not rstOrders.EOF Then
Debug.Print "dup on row " & lRow
Else
sInsert = "INSERT INTO tblOrders ([order], item) VALUES('" &
_
.Cells(lRow, 1).Value & "','" & .Cells(lRow, 2).Value &
"')"

conOrders.Execute sInsert
End If

lRow = lRow + 1

rstOrders.Close

Loop
End With

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO, pls help! 3
code to mark coloured cells in excel 8
Displaing result in Entire worksheet 4
userform duplicate control not working in my code. Thank you 13
Code reqd. to generate Loading Slip 3
Help with code issues 0
I need some changes in a code 5
Need error trap 4

Top