Export Table to XML from Access97

Is there anyway to programatically export a Table/Query in Access97 to an XML file?
High points for a speedy solution!
SOTAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew_WebsterCommented:
Option 1:
Go round the block a little: write code to export to Excel, the automate opening it in Excel and saving as XML.

Option2:
Check this page, about halfway down is a possible solution - http://www.vb123.com/toolshed/01_docs/accessxml.htm
SOTAAuthor Commented:
The problem is if I export to Excel, I am losing data as TEXT fields contain carriage returns, and Excel limits the characters to 255. So, I need to go from Access97 directly to XML.

I found this code but it fails on DEBUG in Access 97. I did make a referecne to ADO 2.8

Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
 
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset

  Dim sql As String
  sql = "Addresses"
 
 
  With cmd
     .ActiveConnection = CurrentProject.Connection
     .CommandType = adCmdTable
     .CommandText = sql
     Set rs = .Execute
   End With
   
  'persist the recordset as xml
   rs.Save "C:\Table1.xml", adPersistXML
  rs.Close

Set cmd = Nothing
Set rs = Nothing

If fails at .ActiveConnection = CurrentProject.Connection with 'Variable not found'. I am clueless as to why.
SOTAAuthor Commented:
Also, on that link it says 'To convert it to Access 97, you would need to provided your own connection string for the current database.' Don't know exactly what to do with that. I am a novice programmer...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Andrew_WebsterCommented:
Oh.  Right.

Well, you've been lumbered with a non-novice problem I'm afraid.  That said, getting a connection string isn't too tricky.  It's basically a bunch of parameters that tell whatever needs to read them "Find you data in this database, on this kind of database platform, using this username and password to get and, and if we need to, here's a couple of other things you'll need to know".

Does that make sense?

So, there is an awesome resource you can use at http://www.connectionstrings.com/ that should sort you out.

If that's all too hard, then maybe you'll need to export to Excel, and save as XML from Excel, and create a manual procedure (i.e. instructions written in English to remind you) for next time, until you become a more experienced developer, or they upgrade Access to a more recent (and still supported!) version, or find someone else to hack it our for you.  (Don't look at me, I do this for fun now!)

Does that help?
SOTAAuthor Commented:
I guess what I do not understand is the table I want to Export is a table already in the Access97 mdb. Why do I need a connection string to connect to an object that is already connected? I do not know how to connect to the 'Current Database'. Surely this is simple??

In DAO I use:

Set db = DBEngine.Workspaces(0).Databases(0)   ' Create database reference.
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)

???
Andrew_WebsterCommented:
Don't worry about that bit.  Read further down the page.  The key thing is to open an ADO recordset so that you can use

    rst.Save cachedir & datasource & ".xml", adPersistXML

or  in your case
    rst.Save "PathToAFolder\" & "FileName" & ".xml", adPersistXML

You'd obviously tweak these to your situation.

The point is to load the table into an ADO recordset, so that you can use the recordset's Save method with the adPersistXML parameter to achieve what you're after.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SOTAAuthor Commented:
OK, I finally got it to work!!

Dim cboStr As String, cancel As Integer
Dim strConnect As String
Dim Rst As ADODB.Recordset
Dim fso, folder
Dim cachedir As String, datasource As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\New Sota\Client_SOTA_Dev.mdb" & ";" & _
"Jet OLEDB:Database Password="

Set Rst = New ADODB.Recordset
Rst.ActiveConnection = strConnect
Rst.Open "LTQueryToWeb_Filtered"
Kill "C:/test.xml"
Rst.Save "C:/test.xml", adPersistXML

Thanks for your help!!
SOTAAuthor Commented:
Awesome!!
Andrew_WebsterCommented:
Excellent.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.