Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO recordset as XML (97/SR2)

    How do you make the contents of a table the same as a recordset read in from XML file ?
    - I want to back up a table, so have used the ADO .Save to persist it to disk in XML format, I can then open it but I can't seem to get the data back into the table. Originally I was using the .UpdateBatch and check the .Status was adRecUnmodified but when I tried a test, i.e. deleted all the records from the original table and tried the load, no records were inserted <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    I know I can do this other ways, even making SQL INSERT strings, but as ADO is the Microsoft way, I thought this simple exercise would be a good start <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    As a further question, how does the XML file store the status of changed records <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO recordset as XML (97/SR2)

    The method you want is .UpdateBatch.

    Here's a routine you can adapt to your uses. This was written for Access 2000, so it uses CurrentProject.Connection, which will fail in A97 and which will have to be replaced with an alternate connection method. The A97 provider would be Jet 3.51, of course.

    <pre>Public Function SyncRecordset(ByVal strPersistedName As String, _
    ByVal strFilePath As String) As Boolean
    On Error GoTo Proc_err
    'update the strRowSource from the persisted recordset
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim errCurr As ADODB.Error

    'open a connection to the database
    Set cnn = CurrentProject.Connection
    'for other than the current db you could use:
    ' Set cnn = New ADODB.Connection
    ' With cnn
    ' .Provider = "Microsoft.Jet.OLEDB.4.0"
    ' .ConnectionString = "data source=" _
    & strFilePath & "PersistedRst.mdb"
    ' .Mode = adModeReadWrite
    ' .Open
    ' End With

    'open the persisted recordset
    Set rst = New ADODB.Recordset
    With rst
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open strFilePath & "" & strPersistedName, cnn, , , adCmdFile
    'You can set the activeconnection property
    'separately by attaching the rst to an active
    'connection instead of in the open method
    ' .ActiveConnection = cnn
    'NOTE: This must come AFTER opening
    ' the persisted recordset

    'Using this will cause at least
    'some syncs to fail
    '.Filter = adFilterAffectedRecords
    If Not .BOF And Not .EOF Then
    'update the source with all records from the recordset
    .UpdateBatch
    SyncRecordset = True
    Else
    MsgBox "There are no records eligible to sync."
    End If

    End With
    Proc_exit:
    On Error Resume Next
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function
    Proc_err:
    If cnn.Errors.Count > 0 Then
    For Each errCurr In cnn.Errors
    MsgBox "ADO error " & errCurr.Number & "--" & errCurr.Description
    Next errCurr
    cnn.Errors.Clear
    ElseIf Err <> 0 Then
    MsgBox "application error " & Err.Number & "--" & Err.Description
    End If
    Resume Proc_exit
    End Function</pre>

    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset as XML (97/SR2)

    Thanks Charlotte
    Changed the code for A97 and 3.51, but it doesn't do what I want <img src=/S/frown.gif border=0 alt=frown width=15 height=15>, or the much more likely, it doesn't do want I think it should
    i.e. if I delete all the records in the access table, then run the SyncRecordset against the XML file, would the .UpdateBatch create new records <img src=/S/question.gif border=0 alt=question width=15 height=15>
    or do I have to create a recordset from the XML, then loop through every record, doing an .AddNew for each <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - this seems a poor approach
    - I suppose I'm wondering if there is someway to make the 'table' equal the recordset in one operation

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO recordset as XML (97/SR2)

    I don't understand the question. What are you *trying* to do. You can keep editing the XML recordset until you have it the way you want it. If you delete records in the XML recordset, they'll be deleted in Access when you sync the two, and the same is true of adding records in the XML recordset. If you delete records in Access rather than from the persisted recordset, you'll probably run into errors and may not be able to sync the table at all.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset as XML (97/SR2)

    Ah, you last comment may explain my problem
    - I need to save the data from one of the tables, then maybe reload it from the data file into the table at a later time, so I'm trying to put together some that will take the data from the file and either insert or amend the records into the Access table
    - main reason is to persist values for different users on different sites, whenever I give them a new copy of the database (I know it'd be better to split into a front-end + data back-end, but I've got to work with the practices set-up previously)
    - I went with the XML as it gave me an opportunity to play with ADO

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO recordset as XML (97/SR2)

    It sounds like you may need to look into replication rather than ADO, but DO NOT try replication on an unsplit database because you don't want to replicate the front end, only the data. Trust me, I've replicated front ends and it is only doable if all your replicas are within walking distance. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    As for working with practices previously set up, don't fall into that trap. It's a good way to lose your sanity fast and you're the one who gets the blame for failures, not the person who set it up in the first place. Persuade them that "advances in the technology" now make it more desireable to split the database. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset as XML (97/SR2)

    Many thanks Charlotte, as ever, very useful advice <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •