Database Search

match allmatch any Look in all areasLook in current areas Check all datesPrevious dayPrevious 3 daysPrevious weekPrevious fortnightPrevious monthPrevious 2 monthsPrevious 3 monthsPrevious half-yearPrevious year

TechHome

• ADO Reading XML Data
• Creating User-Defined Types Using XML Document Object Model
• DBF to XML Converter
• Edit an Existing XML File
• Get First Column of Data from an Excel Spreadsheet
• MSXML Transformation via OLE
• Read XML Row by Row Based on Namespace String
• Replace Existing Element with New Data
• Soundex UDF
• Uses XML DOM and DHTML to Create a DSO-like editable HTML display
• XML Recordset and Excel Named Ranges
• XML Recordset Editing Script
• XML Recordset Primer
• XML Recordset to MDB
• XPATH and XML Parsing

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

XML Recordset to MDB

---

Question:

Does anyone know how to save an xml recordset as an access mdb file using ADO?

Answer:

I do not believe you can Save or Save As directly from XML ( maybe possible in Office 2003 ). You can either

(1) write a loop to INSERT each xml row into a corresponding Access Table
(2) get real tricky and fake Access into thinking the XML file is really a disconnected RecordSet then issue UpdateBatch()

Note: You can probably build on the attached script. It performs the equivalent of a 'copy structure to' in some older languages. Now I have heard, that if you have a blank Access table structure similar to your XML, your can open the XML as a Recordset [keyset,BatchOptimistic] - open the Table as an ADODB.Connection, set the Recrodset ActiveConnection property to the table connection, then issue

RS.UpdateBatch()

and the Access Table will fill with the XML records - which beats a loop.

;XML2ACC.WBT 

;// Winbatch -
;// Simple method to create Access Table
;// based on structure of persisted XML Recordset
;//
;// NOTE: assume XML file was persisted with RS.Save()
;//
;//
;// Stan Littlefield - May 3, 2003
;/////////////////////////////////////////////////
#DefineFunction cvtstru(RS,cat,tblName)
tbls  = cat.Tables
tbl   = ObjectOpen("ADOX.Table")
tbl.Name = tblName
cols = tbl.Columns

flds = RS.Fields
n = flds.Count

For i = 0 To (n-1)
   fld = RS.Fields(i)
   ;// this is a bare bones conversion
   ;// does not take into account field length for text
   ;// or field properties such as isNullable...
   BoxText(StrCat("Creating ",fld.Name))
   cols.append(fld.Name,fld.Type)
Next
tbls.append(tbl)
ObjectClose(tbl)
ObjectClose(tbls)

#EndFunction

;// table will be built in a temp MDB file
cMDB  = StrCat( dirget(),"xmltemp.mdb")
If FileExist( cMDB ) Then FileDelete( cMDB )

cXML   = AskFileName("Select XML File",".\","XML Files|*.xml|","*.xml",1)
If cXML == "" Then Exit
BoxOpen("Please Be Patient","Converting Recordset To Access Table")
RS = ObjectOpen("ADODB.RecordSet")
RS.Open(cXML,"Provider=MSPersist;",,,256)
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%"

;// use ADOX to create temp file
cat   = ObjectOpen("ADOX.Catalog")
cat.create(cConn)
cat.ActiveConnection = cConn
;// pass XML Recordset, ADOX catalog and
;// table name to UDF
cvtstru(RS,cat,"MyXMLStructure")
ObjectClose(cat)
RS.Close()
ObjectClose(RS)
BoxShut()
Exit

---

Article ID:   W16158

File Created: 2004:03:30:15:43:00

Last Updated: 2004:03:30:15:43:00