ADO Persisted recordset xml file

Posted on 2007-10-10
Last Modified: 2012-06-27
I have to process some xml files using vbscript. the xml files are created elsewhere, and are a persisted ado recordset and contain many rows of data.

These xml files have been created using the following code:
oRs.Save strFileName, 1

I can open the file and read the xml file into an ado recordset, but how then can I "save" this recordset to my database?
Question by:unlocktechnology
    LVL 5

    Expert Comment

    Depends on what data you need to save from the recordset data.

    You could just iterate the rows in the recordset and create INSERT statements into the table you require.
    LVL 17

    Expert Comment

    The problem here is that there is a bug in SQL Server that if you upload the whole recordset to the server in one hit it will only allow you to send a few thousand rows.

    So for months so you can think that your app is working fine, then one day somebody notices that records are missing, but by then it is too late because all of the data that provided the records have been dropped. You won;t want o be arround when they discover it was your app that failed. Don't go there it will not work reliably!!!!!

    But if you promise to only use small batches of data I will post the code for you...

    It is possible to create sql insert statements from the recordset to create SQL transactions which is the best way to go.
    LVL 17

    Accepted Solution

    Example how to save load xml and save data.

    In this example I used a library class zADO  which simplefies some ADO functions that you already know how to do like create a database and execute a sql query.

    Private Sub Form_Load()
    Dim ADO As New zADO
    Dim GF As New zGF
    Dim OK
    Dim SQL As String

    ' create a disconnected recordset

    Dim RS As ADODB.Recordset
    Set RS = New ADODB.Recordset
    RS.Fields.Append "FText", adVarChar, 50
    RS.Fields.Append "FInt", adInteger
    RS.Fields.Append "FDate", adDate
    Set RS.ActiveConnection = Nothing
    Dim lc As Long

    ' create some test data
    For lc = 1 To 100
        RS("FText") = "MyText:" + CStr(lc)
        RS("Fint") = lc
        RS("FDate") = Now

    ' save the data as xml
    ' delete the file if exists already
    OK = GF.KillFileOK("d:\test.xml")
    RS.Save "d:\test.xml", adPersistXML

    ' make sure rs is emptied
    ' clear the data
    Set RS = Nothing

    ' reload the data
    Set RS = New ADODB.Recordset
    RS.Open "d:\test.xml"

    ' print the data to prove it came back
    Do While Not RS.EOF
        Debug.Print RS("FInt"); " "; RS("Fdate")

    ' create a database mdb and then connect to it

    Dim CN As ADODB.Connection
    OK = GF.KillFileOK("d:\test.mdb")
    OK = ADO.CreateMDBOK("D:\TEST.MDB", Jet4x)

    ADO.RegisterMDB "D:\TEST.MDB"

    OK = ADO.ConnectOK(CN)
    If Not OK Then
    End If

    ' create a table
    SQL = "Create Table [MyTable](ID Identity, FText nVarChar, FInt Int, FDate DateTime);"
    OK = ADO.ExecuteSQLOK(CN, SQL)
    If Not OK Then
        MsgBox ADO.ErrD
    End If

    Dim RS2 As ADODB.Recordset

    ' open the table on RS2
    Set RS2 = New ADODB.Recordset

    RS2.CursorLocation = adUseClient
    Set RS2.ActiveConnection = CN
    RS2.Source = "Select Top 1 * From [MyTable]"
    RS2.LockType = adLockBatchOptimistic
    RS2.CursorType = adOpenStatic

    ' move the data from RS into RS2
    Do While Not RS.EOF
        For lc = 0 To RS.Fields.Count - 1
            RS2(RS.Fields(lc).Name) = RS(lc)

    MsgBox "Job Done"

    End Sub
    LVL 17

    Expert Comment

    Special note; in the following line:

    RS2.Source = "Select Top 1 * From [MyTable]"

    If you forget the TOP 1, and your table has a few million records, it will take a long time to complete.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now