How to use ADO Seek without using Primary key as Index

[ac]

Hi all, I have given up on computer after my do-while loops causes my PC to
hang constantly,
Would appreciate your kind advice. cheers, ac.

I have 2 tables:
table 1(lookup table):
Col0 Col1 Col2 Col3
N1 A B
N2 C A
N3 B A C
Table 2 (Very Big Tables):
Col0 Col1 Col2 Col3 Col4
A B xx
C xx A xx
B A C
......................................................
xx = any data.

I want to base on table 1 (not based on table2) and go through each record
and do a match
with table 2's Col1,Col2,Col3 and update the the table2!Col0 with
table1!Col0, if the criteria matched.

So i presume i need to use seek and do while loop. My questions are:
1. Is there a way to use a combinations of indexed fields instead of
Primary Key
for seek in ADO? how can i do it?
2. How can i nest the Do while and Seek command, since i think i need 2
Do-while-Loop to go through all records in table 2 and table1. Sample codes
below:

Dim cnn1 As ADODB.Connection, rst1 As New ADODB.Recordset,
Dim rst2 As New ADODB.Recordset, Dim intTotalRec As Long,
Dim strDirectory As String

strDirectory = CurrentProject.Path & "\"

cnn1.Open "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDirectory & "Test.MDB;"
rst1.Open "tbl1", cnn1, adOpenKeyset, adLockOptimistic
rst2.Open "tbl2", cnn1, adOpenKeyset, adLockOptimistic

rst1.Index = "PrimaryKey" ' how can i use the indexed key here instead
of primary key??
Do Until rst2.EOF = True ' i have blank entries in table 1, may casue
problem for seek commad??
rst1.MoveFirst
Do Until rst1.EOF = True
rst1.Seek Array(rst1!col1, rst1!col2, rst1!col3), adSeekFirstEQ
............ i am lost here ........

 

[Michel Walsh]

Hi,

Somehow, if I understand the question, it can be a matter of a criteria that would look like


(with DAO)
FaxNumber LIKE Country_AreaCode & "*"

or

(with ADO)
FaxNumber LIKE Country_AreaCode & "%"





Hoping it may help,
Vanderghast, Access MVP