Solved: Unable to update data in db using ADODB connection

[Thagor]

Hi,

I have the following code in a VBA macro. It connects to a database instance and tries to create a new record/row of data. I run this, it runs ok but does not update the table.

Dim conn As ADODB.Connection
Dim stADO As String
Dim recordSet As ADODB.RecordSet

stADO = "Provider=SQLOLEDB;Initial Catalog=FooDb; Server=fooServer;User Id=fooUser; Password=fooPwd"

Set conn = CreateObject("ADODB.Connection")

conn.Open stADO

Set recordSet = New ADODB.recordSet
With recordSet
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "SELECT * FROM DriversTbl", conn
.AddNew
.Fields("Driver") = "foo"
.Fields("ProgID") = "foo"
End With

recordSet.Update
recordSet.Close
conn.Close

I am using SQLServer 2005, with the same user account and password - if I use Microsoft SQL Server Management tool - I can create update queries ok - so I do have permissions to read, write and modify data.

So any ideas why this runs ok - but does not update the database would be created appreciated :-)

Many thanks

[austenr]

are you sure you have all your references set correctly?

[Thagor]

Yes, it worked :-) Didn't change anything, but I had a manic day yesterday - I checked the database today and um, there were a few test rows :-) For some reason it did not refresh and pickup the new rows. Thanks for getting me to relook at it though :-)