read()

[Ringers]

I have created the following code in access 2003. i am trying to create a sql connection and export the data to a XML file and I don't know how to do it.

I have got the connection code for SQL database, but what is the next step a read() method? If anyone can point me in the right direction, thanks.

Private Sub Command0_Click()

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\chicks\My Documents\EDIS\Databases\Mercy EDIS\db1.mdb;"

oConn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\chicks\My Documents\EDIS\Databases\Mercy EDIS\db1.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"sa", "!sa789"

'Set Connection = MercyData.OpenConnection (

' Export the contents of the Customers table. The Orders and Order
' Details tables will be included in the XML file.
Application.ExportXML ObjectType:=acExportTable, DataSource:="F_Pat_Present", _
DataTarget:="Mercy.xml"

End Sub

 

[CautionMP]

Ringers,
I think you mean Open(), not Read(), to get a recordset you can then export to XML.

I'm not sure if ExportXML() will take an SQL string as the source (most of the DoCmd routines will only use a saved database object), so here is some code that will make the file using ADO.

Private Sub Command0_Click()
  Dim conSource As ADODB.Connection
  Dim rstSource As ADODB.Recordset
  
  Set conSource = New ADODB.Connection
  conSource.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Documents and Settings\chicks\My Documents\EDIS\Databases\Mercy EDIS\db1.mdb;" & _
           "Jet OLEDB:System Database=MySystem.mdw", _
           "sa", "!sa789"
  
  Set rstSource = New ADODB.Recordset
  rstSource.Open "SELECT * FROM F_Pat_Present;", conSource
  rstSource.Save "C:\Mercy.xml", adPersistXML
  rstSource.Close
  conSource.Close
  Set rstSource = Nothing
  Set conSource = Nothing

Since this uses early binding be sure to add a reference to one of the ADO libraries.

Hope this helps,
CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)