ADODB Recordset Updating

• normturgeon

  Old Hand

  Points: 337

  More actions

  October 2, 2013 at 9:48 am

  #395589

  The following MSAccess VBA code worked well with SQL Server 2005, but produces an error with SQLServer 2012.

  The error is #3251 "Current recordset does not support updating".

  Set gCnn = New ADODB.Connection

  gCnn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=myCOMPUTER\mySERVER;DSN=mydataDSN;Database=myData;UID=medb;PWD=abc123;Trusted_Connection=yes;"

  gCnn.Open

  Dim rst As ADODB.Recordset

  Dim sql As String

  Set rst = New ADODB.Recordset

  rst.CursorLocation = adUseClient

  sql = "SELECT * FROM tblQuoteLob WHERE (QuoteID = " & Me!QuoteID & ")"

  rst.Open sql, gCnn, adOpenKeyset, adLockOptimistic

  If rst.EOF And rst.BOF Then

  'add a new record

  rst.AddNew

  rst!QuoteID = Me!QuoteID

  rst.Update

  End If

  rst.Close

  Set rst = Nothing

  Any Ideas?

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  October 2, 2013 at 10:21 am

  #1655266

  Most likely you need to change your cursor location to adUseServer.

  Also, NEVER NEVER NEVER post you actual connection information to a public forum. I would advise you to change your original post and change the username and password.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/