Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 3rd, 2007, 01:48 AM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I export an access db table to xml?

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.

Reply With Quote
  #2 (permalink)  
Old April 3rd, 2007, 03:17 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

' 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
Reply With Quote
  #3 (permalink)  
Old April 3rd, 2007, 10:54 AM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #4 (permalink)  
Old April 3rd, 2007, 05:44 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old April 3rd, 2007, 11:56 PM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Mircosoft Excel Data to Access Table tiyyob Excel VBA 0 January 11th, 2006 06:19 AM
How to Export SQL Server Table to XML file??? bekim XML 1 October 4th, 2004 03:52 AM
Export Table from Access to Excel aramchan Access VBA 2 July 12th, 2004 03:20 PM
Export table to txt in Access XP midtowncreek Access 9 May 24th, 2004 08:02 AM
Export to XML from DB HamishF SQL Server 2000 8 December 21st, 2003 05:42 PM



All times are GMT -4. The time now is 04:03 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.