ADP: A2K vs 2002?
(Pete Cresswell)
& Noble and came across a statement to the effect of:
"The implementation of ADP in Access 2000 was sort of like MS Access
1.0, whereas with with Access 2002's implementation you can actually
create real-world applications."
I've been dabbling in Access 2000's ADP and haven't seen any reason
for that statement.....yet...
Comments?
-----------------------
Pete Cresswell
NylonString
with those functions 2000 delivers. We are planning to move on to 2002 as
soon as the users' desktop catching up with their hardware requirements to
support office XP. One of the major features that will greatly benefit us on
2002 is the ability to 'connect' data from multiple SQLs and other
datasources from an ADP.
We have tried to 'convert' from 2000 to 2002, the program works just fine
w/o modifications of any lione of codes.
NylonString
"(Pete Cresswell)" <x@y.z> wrote in message
news:qvhljuse9m3ogbr2h...@4ax.com...
Steve Jorgensen
additional features for writing solid client/server and 3-tier apps -
most significantly, using bound forms with disconnected recordsets and
batch updates.
On further experimentation, though, I found that most of the exciting
new capabilities were unimplemented, and there's an article on the MS
support site explaining that the Access 2002 help is wrong when it
says these functions are available. The fact that the new properties
and methods exist at all, functional or not, though seems to mean MS
was seriously attempting to get the features into XP and will probably
make them work in a service pack or in the next version of Access.
In a way, I would compare Access 2002 to Access 95 in that the basis
for great new functinality is there, just not fully implemented.
Note that I did find 2 very minor improvements that can be used to
major benefit in conjunction with some serious cleverness. Now, you
-can- bind a form to a disconnected recordset -after- it has been
disconnected, and you can now edit data in a disconnected recordset
through the bound controls when you do this. You still can't bind a
manufactured recordset (without cheating).
The reason these improvements are mor minor than they sound are that
Access mangles the Original and Underlying values of fields in the
recordset when you edit, so any later attempts to perform BatchUpdate
are doomed to failure. Attempts to thwart this limitation using undo
and then performing the updates properly in code don't help either
because Access displays the Underlying value of each field in the
bound controls, so updates to Value are not visible.
Now, the clever tricks:
1. Open a static, optimistic batch recordset, disconnect it, save it
to a simple in-memory stream object, set the stream position to zero
so it can be read from the beginning, and open a new copy of the
recordset from the stream. Now, bind one copy to the form, and start
working. When you're ready to do a batch update, first compare the 2
recordsets for changes, and apply any adds, changes, and deletes to
the unbound recordset, then run BatchUpdate from the unbound
recordset. Note that Access sets values of IDENTITY columns to zero,
so that's an easy way to filter for added records.
2. To turn a fabricated recordset into a fake disconnected recordset
that can be successfully bound to a form, you have to add BASETABLE
and BASECOLUMN properties to each field where the BASETABLE values are
all the same and BASECOLUMN values are the same as the field names (or
just arbitrary, different strings). Since ADO gives no direct access
to these properties, we have to cheat.
Persist the recordset to a text stream as XML, then use the DOM to add
rs:basetable and rs:basecolumn attributes to each "AttributeType"
element, then copy the XML back to a stream and open a recordset from
that. You can now bind the recordset to a form.
Note that these tricks work equally well with MDBs and ADPs.
Here's a trivial case example of the code trick #1 in which the only
fields are an IDENTITY column named TestID, and a 4-byte integer
column called TestNum. Scroll past this example for an example of
trick #2:
--------------
Dim mrsTest As ADODB.Recordset
Private Sub cmdUpdate_Click()
Dim cnn As ADODB.Connection
Dim rsTestClone As ADODB.Recordset
Set rsTestClone = Me.Recordset
Set rsTestClone = rsTestClone.Clone
If rsTestClone.BOF And rsTestClone.EOF Then
' No records in bound recordset - flag any/all records
' in unbound recordset for deletion to match.
If Not (mrsTest.BOF And mrsTest.EOF) Then
mrsTest.MoveFirst
While Not mrsTest.EOF
mrsTest.Delete
mrsTest.MoveNext
Wend
End If
Else
' Check for edited/deleted rows in bound recordset.
If Not (mrsTest.BOF And mrsTest.EOF) Then
rsTestClone.Sort = "TestID"
mrsTest.MoveFirst
While Not mrsTest.EOF
rsTestClone.MoveFirst
rsTestClone.Find "TestID=" & mrsTest!TestID
If rsTestClone.EOF Then
' No record in bound - deleted.
' Flag for deletion in unbound as well.
mrsTest.Delete
Else
' Record in bound - values edited or untouched.
If rsTestClone!TestNum <> mrsTest!TestNum Then
' value changed - update unbound.
mrsTest!TestNum = rsTestClone!TestNum
End If
End If
mrsTest.MoveNext
Wend
End If
' Check for added rows in bound recordset.
rsTestClone.Filter = "TestID=0"
While Not rsTestClone.EOF
mrsTest.AddNew
mrsTest!TestNum = rsTestClone!TestNum
rsTestClone.MoveNext
Wend
End If
Set rsTestClone = Nothing
Set cnn = New ADODB.Connection
cnn.Open _
"Provider='SQLOLEDB';Data Source='SJSERVER';" _
& "Initial Catalog='Test'"
Set mrsTest.ActiveConnection = cnn
mrsTest.UpdateBatch
Set mrsTest.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rsTest As ADODB.Recordset
Dim stmTest As ADODB.Stream
Set cnn = New ADODB.Connection
cnn.Open _
"Provider='SQLOLEDB';Data Source='SJSERVER';" _
& "Initial Catalog='Test'"
' Create recordset and disconnect it.
Set mrsTest = New ADODB.Recordset
mrsTest.CursorLocation = adUseClient
mrsTest.Open "tblTest", cnn, adOpenStatic, adLockBatchOptimistic
Set mrsTest.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
' Copy recordset to stream in memory.
Set stmTest = New ADODB.Stream
stmTest.Open
mrsTest.Save stmTest, adPersistADTG
' Bind form to disconnected recordset.
Set Me.Recordset = mrsTest
' Open a copy of the recordset from the stream.
Set mrsTest = New ADODB.Recordset
stmTest.Position = 0
mrsTest.Open stmTest
Set rsTest = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Me.Detail.Visible = False
' Close the unbound copy of the recordset.
mrsTest.Close
Set mrsTest = Nothing
' Unbind the from's recordset, and close it.
Set mrsTest = Me.Recordset
Set Me.Recordset = Nothing
mrsTest.Close
Set mrsTest = Nothing
End Sub
--------------
Here's the code I wrote just last night to implement trick #2 - this
code requires a reference to Microsoft XML, 3.0 (or any version should
work):
--------------
Public Sub SCJ_ADOMakeFabRSBoundEd( _
rsFabricated As ADODB.Recordset _
, rsBoundEdit As ADODB.Recordset _
)
Dim stmPersist As ADODB.Stream
Dim xmd As MSXML2.DOMDocument
Dim xmn As MSXML2.IXMLDOMNode
Dim xma As MSXML2.IXMLDOMAttribute
Set stmPersist = New ADODB.Stream
stmPersist.Type = adTypeText
stmPersist.Open
rsFabricated.Save stmPersist, adPersistXML
Set xmd = New MSXML2.DOMDocument
stmPersist.Position = 0
xmd.loadXML stmPersist.ReadText
stmPersist.Close
Set stmPersist = Nothing
For Each xmn In xmd.getElementsByTagName("s:AttributeType")
Set xma = xmd.createAttribute("rs:basetable")
xma.Value = "T"
xmn.Attributes.setNamedItem xma
Set xma = xmd.createAttribute("rs:basecolumn")
xma.Value = xmn.Attributes.getNamedItem("name").nodeValue
xmn.Attributes.setNamedItem xma
Next
Set xma = Nothing
Set xmn = Nothing
Set stmPersist = New ADODB.Stream
stmPersist.Type = adTypeText
stmPersist.Open
stmPersist.WriteText xmd.xml
Set xmd = Nothing
stmPersist.Position = 0
Set rsBoundEdit = New ADODB.Recordset
rsBoundEdit.Open stmPersist
stmPersist.Close
Set stmPersist = Nothing
End Sub
--------------
--
Steve Jorgensen
Database application developer - available
http://www.coho.net/~jorgens