Hi All,

I am using UPDATE query from Excel to Access.

I have 1000 rows with unique IDS ( same IDS in Access Database Table)

I need to UPDATE for 1000 IDS status. Right now I am using like this.

I want to know is there any better option to do this ??

For R = 4 To 1000

ID = Trim(Cells(R, "A").Value)
STS = Trim(Cells(R, "F").Value)

SQL = "UPDATE TRACKER SET TRACKER.STATUS = '" & STS & "'"
SQL = SQL & " WHERE ID = '" & ID & "'"

rs.Open SQL, db, adOpenDynamic, adLockOptimistic, adCmdText

Next R

You would be best to post this in:

comp.databeses.ms-access

They are more atuned to Access related issues even though it is connected to
Excel.

HTH

It depends on your set up and how your app works. But an option that
also have is:
- in the Access create a linked table (linked to the excel)
- create an update query that would update the other access table with
the values from the linked table - the join would be the IDs that you
mention.



On Jan 13, 11:29*pm, "fi.or.jp.de" wrote:
Hi All,

I am using UPDATE query from Excel to Access.

I have 1000 rows with unique IDS ( same IDS in Access Database Table)

I need to UPDATE for 1000 IDS status. Right now I am using like this.

I want to know is there any better option to do this ??

For R = 4 To 1000

ID = Trim(Cells(R, "A").Value)
STS = Trim(Cells(R, "F").Value)

SQL = "UPDATE TRACKER SET TRACKER.STATUS = '" & STS & "'"
SQL = SQL & " WHERE ID = '" & ID & "'"

rs.Open SQL, db, adOpenDynamic, adLockOptimistic, adCmdText

Next R