Why this did not work in Access 2000?

[Min]

Hi, the following query works in Query design

Update Table1 set CompID =1 Where CompName = 'AACompany'

But, for a recordset in a form module: rsComp.Update set CompID =1 Where
CompName = 'AACompany' it does not work. Anyone can help? How to write the
query for Recordset.Update? I just cannot find sample code.

Dim cnn As ADODB.Connection
Dim rsTable1 As ADODB.Recordset
Dim strSQL As String

strSQL = "Select CompName, CompID From Table1 order by CompName"

Set cnn = CurrentProject.Connection
Set rsTable1 = New ADODB.Recordset
rsTable1.ActiveConnection = cnn
rsTable1.CursorLocation = adUseClient
rsTable1.Open strSQL, , adOpenDynamic, adLockOptimistic


rsComp.Update set CompID = 1 Where CompName = 'AACompany'

 

[Dirk Goldgar]

Hi, the following query works in Query design

Update Table1 set CompID =1 Where CompName = 'AACompany'

But, for a recordset in a form module: rsComp.Update set CompID =1
Where CompName = 'AACompany' it does not work. Anyone can help? How
to write the query for Recordset.Update? I just cannot find sample
code.

Dim cnn As ADODB.Connection
Dim rsTable1 As ADODB.Recordset
Dim strSQL As String

strSQL = "Select CompName, CompID From Table1 order by CompName"

Set cnn = CurrentProject.Connection
Set rsTable1 = New ADODB.Recordset
rsTable1.ActiveConnection = cnn
rsTable1.CursorLocation = adUseClient
rsTable1.Open strSQL, , adOpenDynamic, adLockOptimistic


rsComp.Update set CompID = 1 Where CompName = 'AACompany'

That's not the way the ADODB.Recordset.Update method works. If you
want to use a recordset, you loop through the recordset to find each
record that meets your criterion, then modify the value of the
rsTable1!CompID field, and the .Update that record -- continuing until
all records have been modified. But that is a very inefficient way to
do it.

Much more efficient would be just to execute the update query directly:

Set cnn = CurrentProject.Connection

cnn.Execute _
"Update Table1 set CompID =1 Where CompName = 'AACompany'"

 

[Min]

Sorry, the code should be:

strSQL = "set CompID = 1 Where CompName = 'AACompany'"
rsComp.Update strSQL

It did not work.

Hi, the following query works in Query design

Update Table1 set CompID =1 Where CompName = 'AACompany'

But, for a recordset in a form module: rsComp.Update set CompID =1 Where
CompName = 'AACompany' it does not work. Anyone can help? How to write the
query for Recordset.Update? I just cannot find sample code.

Dim cnn As ADODB.Connection
Dim rsTable1 As ADODB.Recordset
Dim strSQL As String

strSQL = "Select CompName, CompID From Table1 order by CompName"

Set cnn = CurrentProject.Connection
Set rsTable1 = New ADODB.Recordset
rsTable1.ActiveConnection = cnn
rsTable1.CursorLocation = adUseClient
rsTable1.Open strSQL, , adOpenDynamic, adLockOptimistic

strSQL = "set CompID = 1 Where CompName = 'AACompany'"

 

[Min]

Thank you!