Make read-only recordset updateable

[poflynn]

I have a query that by nature is read-only. This data is used to populate a form in datasheet mode.

Somehow I need to be able to edit the entry and update the source table. Any Ideas.

I appreciate the fact that I will need vba code to do the updating. that is no problem.

I have tried to create a disconnected recordset and clone a recordset they all come out readonly.

The main thing is to be able to able to change the entry.

I have also considered using the readonly form as a subform and use the onclick event to update text boxes on the parent form. this will work but it is not the solution I was hoping for

Regards Phill

ps my current code :

Private Sub Form_Open(Cancel As Integer)

'On Error Resume Next

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim query As String

Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & CurrentDb.Name
If err <> 0 Then
MsgBox "A connection error has occured." & vbNewLine & err.Description, vbCritical
err = 0
Exit Sub
End If
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Properties



query = "SELECT Item_Query.PHANDLE, Item_Query.TEXTSTRING AS ITEM, Descr_Query.TEXTSTRING AS DESCR " & _
"FROM Item_Query INNER JOIN Descr_Query ON Item_Query.PHANDLE=Descr_Query.PHANDLE;"

rst.Open query, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Set Me.Recordset = rst



End Sub

 

[boblarson]

Why not open the query as NOT read-only and just use code to lock the controls until you need to edit?

 

[poflynn]

I'm not sure how to achieve this.

Essentially. I have a table that contains a column whose entries become columns. the inital table is an output from Autocad an as such can't be changed. I am trying to present the data in a more user-friendly, useable format

Phill

 

[Pat Hartman]

What is making the query not updatable? You are showing the final query which looks fine but what about the two queries that you are joining?

 

[poflynn]

Solution

The query is by nature read only

but... After much hunting here is the solution

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM EqptListQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst.ActiveConnection = Nothing (makes the record set updateable)
Set Me.Recordset = rst


Then I just use an update query to update the table when a field changes

Thanks All!!