How do I export an access db table to xml?

solos · April 3rd, 2007, 01:48 AM

I'm trying to accomplish this through a vb module in an access db and frankly have no idea where to start..I would like to export an xml file of a particular table...the db is access 2003...i guess I really need to know if there is a vb command for creating xml..any guidance appreciated.

Shasur · April 3rd, 2007, 03:17 AM

' This can be executed using VBA. You need to add Microsott Access 11.0 Library
Sub Export_AccessTable_2_XML()

Dim oAC As New Access.Application

oAC.OpenCurrentDatabase "C:\temp\Test.mdb"

oAC.ExportXML _
    ObjectType:=acExportTable, _
    DataSource:="Test", _
    DataTarget:="C:\temp\Test.xml", _
    SchemaTarget:="C:\temp\TestSchema.xml"

End Sub

Cheers
Shasur

http://www.vbadud.blogspot.com

solos · April 3rd, 2007, 10:54 AM

this is being run from a module..I get database is already open exclusively by another user...sorry Im a newb at vb....how can I set oAC.OpenCurrentDatabase to the current db w/o a relative path?

solos · April 3rd, 2007, 05:44 PM

ok...following is how I've worked through this...what i don't understand is why I cant print the recordset results without including the GetValue command...is there a better way to print the results of my recordset?

Code:

Public Sub WebExtract()
Dim ds As DAO.Recordset
Dim SQL As String
Dim fso
Dim act
Dim file_being_created
Dim oAC As New Access.Application
Set fso = CreateObject("scripting.filesystemobject")
SQL = "SELECT * FROM Inventory WHERE DisplayOnWeb=true;"
Set ds = CurrentDb.OpenRecordset(SQL)
' Writes the db output to a .xml file in the same directory
Set act = fso.CreateTextFile("c:\web_items.xml", True)
' All non repetitive xml on top goes here
act.WriteLine ("<?xml version=""1.0"" encoding=""ISO-8859-1""?>")
act.WriteLine ("<?xml-stylesheet type=""text/xsl"" href=""web_items.xsl""?>")
act.WriteLine ("<web_inventory>")
' Meat and potatoes
While Not ds.EOF
act.WriteLine ("<item id=" + "'" + GetValue(ds![Item Number]) + "'" + ">")
act.WriteLine ("<description>" + GetValue(ds!Description) + "</description>")
act.WriteLine ("<Cost>" + GetValue(ds!Cost) + "</Cost>")
act.WriteLine ("</item>")
ds.MoveNext
DoEvents
Wend
ds.Close
'close tags
act.WriteLine ("</web_inventory>")
End Sub

Raghavendra_Mudugal · April 3rd, 2007, 11:56 PM

one line will do a lot of work...
rst.open (<open up the sql statement as you need the data>)

-> here-> rst.Save App.Path & "\data1.xml", adPersistXML (this one line)
Its called FRO,check it out for more useful help.

Hope this helps.

With Regards,
Raghavendra Mudugal