October 2, 2013 at 9:48 am
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?
October 2, 2013 at 10:21 am
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply