Vb6 current recordset does not support updating
Enabling varying rows and columns involves using a data structure known as a ragged array-essentially an array of arrays-which can be cumbersome and counterintuitive to develop against.
The advantage of using a custom recordset object is that we can present the data in a way that is familiar to most programmers, but we also get full control of what is happening inside the recordset.
Firstly a "normal" select statement won't return an updatable recordset, so attempting to update it won't update the underlying table.
Use a Recordset as such: Dim rs as new ADODB.recordset dim conn as new ADODB.connection dim str SQL as string dim connection String as String /* Fill out connectionstring stuff etc connectionstring rs.
Although I don't fully understand your sample code, you seem to be opening one recordset for the Access table and another for the Excel worksheet, then moving through the Excel recordset and inserting (. I am currently having problems with adding a new record and deleting a record. Cursor Location = ad Use Client 'Open the Connection cn1. ", _ vb Question vb Yes No vb Default Button2, _ "Delete Record") _ vb Yes Then Exit Sub End If With rs1 . Move Last Get Fields End Sub Private Sub cmd Next_Click() On Error Resume Next ' Go to next record - rs1. Open method and supply a Lock Type Enum value that isn't ad Lock Read Only instead of using the Connection. (I'm assuming that the Access ADO provider allows this, I haven't actually tried it myself.) should be able to supply just the name of the table instead of a sql statement which may be more suitable for what you are doing. Its written in VB6 and connects to Access 2003 via ADO. Move Last Get Fields End Sub Private Sub cmd Delete_Click() If Msg Box("Are you sure you want to delete this record? Move First Get Fields End Sub Private Sub cmd Last_Click() On Error Resume Next ' Go to last record - rs1. To solve the first issue you probably need to use the Recordset.Updating a recordset will have no effect on the central data source until you call the Update Recordset method on the DAL.
We could have easily returned an array of data from our DAL, especially since ADO and RDO nicely support Get Rows, which does exactly that, but arrays are limited in a number of ways. In Visual Basic, you can resize only the last dimension of an array, so forget about adding columns easily. Retrieving information from an array means relying on such hideous devices as constants for field names and the associated constant maintenance, or the low-performance method of looping through indexes looking for fields. This may be a limitation of the provider, or of the selected locktype.