Hi,
Below is the scenario.
I am tyring to updates the column values in my access table by matching the ID from SQL tables. If it matches pull records from table 1 or else table 2
Somehow this query is taking 10 mins to refresh 440 records into the access table.
AccessTable A has only 1 column (col_pk) while I'm tyring to match the same column from other tables, and if it matches then add new column and its respective records.
Do I have any levy of optimizing the query?
Code:
strSQL = "SELECT * FROM AccessTable A;"
'440 records currently
X.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do While X.EOF = False
strSQL = "SELECT Col1, Col12, NAME AS Col13, Col14, Col15 FROM dbo.SQL_Table WHERE REP_ID = '" & X("Col_PK") & "';"
'
Y.Open strSQL, DC.BODS_REP, adOpenKeyset, adLockOptimistic
If Y.RecordCount <> 0 Then
X("Col1") = Y("Col1")
X("Col12") = Y("Col12")
X("Col13") = Y("Col13")
X("Col14") = Y("Col14")
X("Col15") = Y("Col15")
X.Update
Y.Close
Else
Y.Close
strSQL = "SELECT MgrID AS Col1, RepStatus As Col12, RepType As Col14 from dbo.SQL_Table2 WHERE RepID = '" & X("Col_PK") & "';"
Y.Open strSQL, DC.BODS_REP, adOpenKeyset, adLockOptimistic
X("Col1") = Y("Col1")
X("Col12") = Y("Col12")
X("Col14") = Y("Col14")
X.Update
Y.Close
End If
X.MoveNext
Loop
X.Close
Record count (SQL_Table)-128,813
Record count (SQL_Table2)-204,218
I think it is a simple query. nothing complex going on. But any ideas/input would be appreciated.
Thanks!