Thagor
09-16-2010, 05:55 AM
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
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