|
|
|
date: Wed, 6 Feb 2008 15:49:01 -0800,
group: microsoft.public.access.odbcclientsvr
back
RE: placing in array into a table using ADO
Alright, I got the table loaded and was able to return a recordset. Now I'm
having trouble comparing items on the spreadsheet to the items in the
recordset. I'm trying to take each item on the ss and and loop it through the
entire contents of the recordset (or actually exit the loop when it finds a
matching value -- there should only be one match)
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim TableName As String
Dim sqlCmd As String
TableName = "GssrTid"
sqlCmd = "CREATE TABLE " & TableName & "(TransactionID VARCHAR)"
msdbCn.Execute CommandText:=sqlCmd
n = 0
For n = 0 To an - 1
s = "INSERT INTO " & TableName & " (TransactionID) VALUES ('" &
tIDArr(n) & "')"
msdbCn.Execute CommandText:=s
Next n
s = "SELECT AsapDrvsPayments.TransactionId,
AsapDrvsPayments.CustTradeId, AsapDrvsPayments.FlowType, "
s = s & "AsapDrvsPayments.FirstLast FROM AsapDrvsPayments "
s = s & "INNER JOIN GssrTid ON AsapDrvsPayments.TransactionId =
GssrTid.TransactionID "
s = s & "GROUP BY AsapDrvsPayments.TransactionId,
AsapDrvsPayments.CustTradeId, "
s = s & "AsapDrvsPayments.FlowType, AsapDrvsPayments.FirstLast;"
Set rs = msdbCn.Execute(CommandText:=s)
n = 0
j = 0
'<<<<this part below is what I'm having trouble with - Is there anyway to
loop through the recordset until I find a value and exit the loop so I don't
have to go through alkl 10K records everytime?..aslo I don't know if this is
going to work If Cells(n, z + 1).Value = rs.Fields(0) ...any ideas?>>>>
For n = 2 To ActiveSheet.UsedRange.Rows.Count
'For j = 1 To rs.RecordCount
rs.MoveFirst
Do Until rs.EOF
If Cells(n, z + 1).Value = rs.Fields(0) Then
Cells(n, z + 2).Value = rs.Fields(1)
Cells(n, z + 3).Value = rs.Fields(2)
Cells(n, z + 4).Value = rs.Fields(3)
else
rs.MoveNext
next n
Much Appreciated.
F
"Frank" wrote:
> Hi,
> I have a one dim array with unique identifiers that I want to load into a
> table to then use to join against a million records, then use that that
> resulting recordset (should be 5K records) to compare against those
> identifiers in the spreadsheet and add the remaining fields back into the
> spreadsheet. For several reasons I don't want to load the entire ss into
> access.
>
> I'm having trouble loading the array into a recordset or table using ADO.
>
> any ideas?
> Thanks
>
>
date: Wed, 6 Feb 2008 20:24:01 -0800
author: Frank
RE: placing in array into a table using ADO
I was able toi get the array in the DB and run the query. The problem now is
comparing the resulting recordset to the values in the spreadsheet -- if
possible I would like to exit the loop on the recordset once I find a
matching value and not continue looking through the 10K items but I'm not
sure that my loops on this last portion works, let alone can become more
efficient.
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim TableName As String
Dim sqlCmd As String
TableName = "GssrTid"
sqlCmd = "CREATE TABLE " & TableName & "(TransactionID VARCHAR)"
msdbCn.Execute CommandText:=sqlCmd
n = 0
For n = 0 To an - 1
s = "INSERT INTO " & TableName & " (TransactionID) VALUES ('" &
tIDArr(n) & "')"
msdbCn.Execute CommandText:=s
Next n
s = "SELECT AsapDrvsPayments.TransactionId,
AsapDrvsPayments.CustTradeId, AsapDrvsPayments.FlowType, "
s = s & "AsapDrvsPayments.FirstLast FROM AsapDrvsPayments "
s = s & "INNER JOIN GssrTid ON AsapDrvsPayments.TransactionId =
GssrTid.TransactionID "
s = s & "GROUP BY AsapDrvsPayments.TransactionId,
AsapDrvsPayments.CustTradeId, "
s = s & "AsapDrvsPayments.FlowType, AsapDrvsPayments.FirstLast;"
Set rs = msdbCn.Execute(CommandText:=s)
<<<<<everything works up to here...how can I compare the array in the
spreadsheet to the recordset in the code below. Many thanks>>>>>
n = 0
j = 0
For n = 2 To ActiveSheet.UsedRange.Rows.Count
'For j = 1 To rs.RecordCount
rs.MoveFirst
Do Until rs.EOF
If Cells(n, z + 1).Value = rs.Fields(0) Then
Cells(n, z + 2).Value = rs.Fields(1)
Cells(n, z + 3).Value = rs.Fields(2)
Cells(n, z + 4).Value = rs.Fields(3)
else
rs.MoveNext
next n
"Frank" wrote:
> Hi,
> I have a one dim array with unique identifiers that I want to load into a
> table to then use to join against a million records, then use that that
> resulting recordset (should be 5K records) to compare against those
> identifiers in the spreadsheet and add the remaining fields back into the
> spreadsheet. For several reasons I don't want to load the entire ss into
> access.
>
> I'm having trouble loading the array into a recordset or table using ADO.
>
> any ideas?
> Thanks
>
>
date: Wed, 6 Feb 2008 20:30:00 -0800
author: Frank
|
|
