[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO Persisted recordset xml file

Posted on 2007-10-10
4
Medium Priority
?
1,003 Views
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?
0
Comment
Question by:unlocktechnology
  • 3
4 Comments
 
LVL 5

Expert Comment

by:RainUK
ID: 20047593
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.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 20049365
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.
0
 
LVL 17

Accepted Solution

by:
inthedark earned 2000 total points
ID: 20049734
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
RS.Open
Dim lc As Long

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

' 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")
    RS.MoveNext
Loop


' 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
    Stop
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
    Stop
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
RS2.Open


' move the data from RS into RS2
RS.MoveFirst
Do While Not RS.EOF
    RS2.AddNew
    For lc = 0 To RS.Fields.Count - 1
        RS2(RS.Fields(lc).Name) = RS(lc)
    Next
    RS2.Update
    RS.MoveNext
Loop
RS2.UpdateBatch
RS2.Close
RS.Close
CN.Close

MsgBox "Job Done"

End Sub
0
 
LVL 17

Expert Comment

by:inthedark
ID: 20054910
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

834 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