Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO, pls help!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO, pls help!
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO, pls help!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO, pls help!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|