Selecting record to edit using ADO

[Ken Valenti]

I am using ADO to edit data in a SQL database.

My problem is how to select a record.

Using "Find" on the recordset works, but takes several minutes.

Using "Excecute" on the connection to run a SQL query only takes seconds
(thanks Tom!), but then the recordset is not editable.

 

[Tim Williams]

Use a different cursor type. The default cursor is read-only.

Or show some code...

Tim

 

[Dave Patrick]

See the notes for LockType

http://msdn.microsoft.com/archive/d...tOfficeDeveloperForumAccessADOProgramming.asp

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I am using ADO to edit data in a SQL database.
|
| My problem is how to select a record.
|
| Using "Find" on the recordset works, but takes several minutes.
|
| Using "Excecute" on the connection to run a SQL query only takes seconds
| (thanks Tom!), but then the recordset is not editable.
|
|

 

[Ken Valenti]

Here's the code.
This finds the record, but is read-only.

Sub ModifyRecord()
Set TheCN = New ADODB.Connection
TheCN.Open TheConnectionString
Set TheRS = TheCN.Execute(SQL_String)
TheRS.Fields(7).Value = "New Value"
TheRS.Update
CloseConnections
End Sub

 

[Dave Patrick]

Try something like;

Public Sub testwrite()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1

'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenDynamic, adLockOptimistic
For i = 1 To 3
rs1.AddNew
rs1!LastName = Sheets("Sheet1").Range("A" & i)
rs1!FirstName = Sheets("Sheet1").Range("B" & i)
i = i + 1
rs1.Update
Next
rs1.Close
cnn.Close
End Sub

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Here's the code.
| This finds the record, but is read-only.
|
| Sub ModifyRecord()
| Set TheCN = New ADODB.Connection
| TheCN.Open TheConnectionString
| Set TheRS = TheCN.Execute(SQL_String)
| TheRS.Fields(7).Value = "New Value"
| TheRS.Update
| CloseConnections
| End Sub

 

[Ken Valenti]

Thanks - works now!

 

[Dave Patrick]

Glad to hear it. You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks - works now!