Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & Loops: error in code?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & Loops: error in code?
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 Santiago wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & Loops: error in code?
Thanks, worked fine. But still don't get it why didn't work.
THANKS!!!!!!!!!!! "Dick Kusleika" wrote: 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 Santiago wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Error and Loops | Excel Programming | |||
Error Handling with Nested Loops | Excel Programming | |||
Error Handling and For-Next Loops | Excel Programming | |||
Help on writing code more efficiently (Loops) | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |