- You can requery the recordset. This has the disadvantage of poor performance.
- You can disconnect the recordset and perform additions and other modifications in parallel with stored procedures. The problem here is that certain fields are read-only, such as Identity and TimeStamp, and you cannot set their value. This becomes problematic if the Identity column is supplied to the stored procedures to identify the record to be updated, or if the TimeStamp column is passed to the stored procedure to determine if someone else has edited the record in the meantime. There are a number of workarounds:
- You can copy the data to a local database, such as a Jet MDB, and use datatypes that do not make the field read-only. You can then update the recordset in parallel to using stored procedures and keep the data in sync.
- You can manufacture a recordset based on the original and copy the data into it. The fields are all updatable and you can update the recordset in parallel to using stored procedures.
|MakeRS||Creates a closed, empty Recordset with fields with the same names and data types as the original. Chaptered fields are not copied.|
|OpenAndFillRS||Opens the destination Recordset and copies data from the (already open) source Recordset. Data in Chaptered fields are not copied.|
The sample application has the following steps:
- It opens a recordset.
- It calls MakeRS to create an equivalent recordset that is not based on any provider and hence does not have any read-only fields.
- It calls OpenAndFillRS to open the recordset and copy the data from the original recordset.
- It closes the original recordset.
- It binds the ADO Data Grid to the manufactured recordset.
- Open a new project in Microsoft Visual Basic 6.0. Form1 is created by default.
- From the Projects menu, choose Components, select the "Microsoft DataGrid Control 6.0 (OLEDB)" control, and place it on the form. Make it fairly large.
- From the Projects menu, choose References, select "Microsoft ActiveX Data Objects 2.1 Library."
- Add the following code to the form module:
Private Sub Command1_Click()
Dim cn As ADODB.Connection, rsSource As ADODB.Recordset, rsDest As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
Set rsSource = cn.Execute("SELECT * FROM Customers")
Set rsDest = MakeRS(rsSource)
OpenAndFillRS rsSource, rsDest
Set DataGrid1.DataSource = rsDest
Function MakeRS(ByVal rsSource As ADODB.Recordset) As ADODB.Recordset
Dim rsTemp As ADODB.Recordset, F As ADODB.Field
Set rsTemp = New ADODB.Recordset
For Each F In rsSource.Fields
If F.Type <> adChapter Then
rsTemp.Fields.Append F.Name, F.Type, F.DefinedSize, F.Attributes And adFldIsNullable
.Precision = F.Precision
.NumericScale = F.NumericScale
Set MakeRS = rsTemp
Sub OpenAndFillRS(ByVal rsSource As ADODB.Recordset, ByVal rsDest As ADODB.Recordset)
Dim F As ADODB.Field
If rsSource.State = adStateClosed Then Exit Sub<BR/>
If rsSource.EOF And rsSource.BOF Then Exit Sub
If rsSource.CursorType <> adOpenForwardOnly Then
If Not rsSource.EOF And Not rsSource.BOF Then
rsDest.CursorLocation = adUseClient
Do While Not rsSource.EOF
For Each F In rsSource.Fields
If F.Type <> adChapter Then rsDest(F.Name).Value = F.Value
- Add a CommandButton, modify the connect string in the Command1_Click procedure, and run the project. The customer's data is displayed in the grid after you click the CommandButton.
ID d'article : 247868 - Dernière mise à jour : 2 juil. 2004 - Révision : 1