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!
High points for a speedy solution!
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.
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.
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?
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?
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).Dat
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
???
In DAO I use:
Set db = DBEngine.Workspaces(0).Dat
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.OL
"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!!
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.OL
"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!!
ASKER
Awesome!!
Excellent.
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