Selecting record to edit using ADO

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken Valenti - 24 Sep 2006 19:51 GMT

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.

Reply to this Message

Tim Williams - 24 Sep 2006 20:36 GMT

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

Or show some code...

Tim

>I am using ADO to edit data in a SQL database.
>
[quoted text clipped - 4 lines]
> Using "Excecute" on the connection to run a SQL query only takes seconds
> (thanks Tom!), but then the recordset is not editable.

Reply to this Message

Ken Valenti - 24 Sep 2006 22:05 GMT

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

> Use a different cursor type. The default cursor is read-only.
>
[quoted text clipped - 10 lines]
> > Using "Excecute" on the connection to run a SQL query only takes seconds
> > (thanks Tom!), but then the recordset is not editable.

Reply to this Message

Dave Patrick - 24 Sep 2006 22:19 GMT

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

Signature

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.
[quoted text clipped - 7 lines]
| CloseConnections
| End Sub

Reply to this Message

Ken Valenti - 24 Sep 2006 22:54 GMT

Thanks - works now!

> Try something like;
>
[quoted text clipped - 45 lines]
> | CloseConnections
> | End Sub

Reply to this Message

Dave Patrick - 24 Sep 2006 22:59 GMT

Glad to hear it. You're welcome.

Signature

Regards,

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

| Thanks - works now!

Reply to this Message

Dave Patrick - 24 Sep 2006 21:34 GMT

See the notes for LockType

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/h
tml/MicrosoftOfficeDeveloperForumAccessADOProgramming.asp

Signature

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.
|
[quoted text clipped - 4 lines]
| Using "Excecute" on the connection to run a SQL query only takes seconds
| (thanks Tom!), but then the recordset is not editable.

Reply to this Message