Link to home
Create AccountLog in
Avatar of SOTA
SOTAFlag for Canada

asked on

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!
Avatar of Andrew_Webster
Andrew_Webster
Flag of United States of America image

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
Avatar of SOTA

ASKER

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.
Avatar of SOTA

ASKER

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...
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?
Avatar of SOTA

ASKER

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)

???
ASKER CERTIFIED SOLUTION
Avatar of Andrew_Webster
Andrew_Webster
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of SOTA

ASKER

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!!
Avatar of SOTA

ASKER

Awesome!!
Excellent.