ADO recordset as XML (97/SR2)

Topic

New Reply

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
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
As a further question, how does the XML file store the status of changed records

Reply | Quote

Replies

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.

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

Reply | Quote

Thanks Charlotte
Changed the code for A97 and 3.51, but it doesn’t do what I want , 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
or do I have to create a recordset from the XML, then loop through every record, doing an .AddNew for each
– this seems a poor approach
– I suppose I’m wondering if there is someway to make the ‘table’ equal the recordset in one operation

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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.

Reply | Quote

Many thanks Charlotte, as ever, very useful advice

Reply | Quote