new ABODB.Connection

[MikeDBMan]

I created an ABODB.connection and then issue this command.

Let QueryTxt = UPDATE ProjectList SET ProcessingStatus = 'This is a test' WHERE 'Project ID' = '1080'
Set cnn = New ADODB.Connection
With cnn
    .Open "mypath\myfile"  ' I know this part is correct.    
    .Execute QueryTxt
End With

Nothing happens. The Table is ProjectList and the 2 fields are both text fields, Project ID and ProcessingStatus.

Suggestions?

 

[Richard Schollar]

Try changing the SQl line to:

Let QueryTxt = "UPDATE ProjectList SET ProcessingStatus = 'This is a test' WHERE [Project ID] = '1080'"

I've put sq brackets round the field name.

 

[MikeDBMan]

That solved my issue. Thanks so much! However, I can't seem to find the "Solved" option to show it as solved?

 

[Richard Schollar]

The forum doesn't show threads as Solved Mike so no need to worry!

We like to keep the threads open in case someone has a better/alternative solution to add

 

[MikeDBMan]

Just wondering. Is there a way to capture a code returned from the external connection to try to determine success or failure of the update?

 

[Richard Schollar]

Sure thing Mike - you need to pass in a long variable with the Execute statement in the RecordsAffected argument:

Dim lngRecsAffected As Long

'...

.Execute QueryTxt, lngRecsAffected, adCmdText  'third argument specifies SQl string being passed in - not strictly necessary

Msgbox lngRecsAffected & " were updated"