Row must equal one of the following, or be deleted
Jamie, Thank you for your response... I am using the simple ado similiar to: Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Acces database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTabl ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub As I reach .Fields("fldFruitType") = Range("K" & r).Value I want t limit those fruits to an array of some sort. However, I am already i the process of adding a new record within the loop. Any help would b appreciated -- samjayne ----------------------------------------------------------------------- samjaynes's Profile: http://www.excelforum.com/member.php...fo&userid=1627 View this thread: http://www.excelforum.com/showthread.php?threadid=27682 |
Row must equal one of the following, or be deleted
samjaynes wrote ...
Thank you for your response... I am using the simple ado similiar to: Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub As I reach .Fields("fldFruitType") = Range("K" & r).Value I want to limit those fruits to an array of some sort. However, I am already in the process of adding a new record within the loop. Any help would be appreciated. Here's a suggestion that uses an array. Replace you existing loop i.e. Do While Len(Range("A" & r).Formula) 0 With rs '... populate rs here End With r = r + 1 Loop with the following: Do While Len(Range("A" & r).Formula) 0 Dim vntResultArray() As String vntResultArray = Filter(Array("apples", "bananas", "oranges", "mangos"), _ Range("K" & r).Value, True, 1) If UBound(vntResultArray) = 0 Then If vntResultArray(0) = Range("K" & r).Value Then With rs '... populate rs here End With End If End If r = r + 1 Loop Jamie. -- |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com