Application.ExportXML

[Petr Jankovsky]

Hello, I have a question about exporting xml using Access. I would like to
export XML data from stored procedure "procedure". This stored procedure have
one parameter "where", lets say
ALTER PROCEDURE procedure(@Where varchar(50)) as
Select * from table where ID = @Where

where table is valid table with column called ID.
I've tried Application.ExportXML acExportStoredProcedure, "procedure",
"C:\xml.xml"
If I run it, input box appears and asks for Where parameter. Is there any
way to pass that parameter via VBA?

 

[Sylvain Lafontaine]

The best way for you might be to directly use the ADO objects; something
like:

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

rs.Open "EXEC test_ExportXML 63", CurrentProject.Connection
rs.Save "C:\test.xml", adPersistXML

 

[Petr Jankovský]

Great, that looks very good. I'm gonna try it soon

 

[Petr Jankovský]

OK, this is working, thanks for solution. But I have another question: It is
possible to specify schema file for that output xml?

 

[Sylvain Lafontaine]

Sorry, I don't know. The only thing that I know is that you cannot call a
stored procedure with parameters with the Application.ExportXML method.

Another solution for you could be to use a form or a report to call the SP
and then use Application.ExportXML method on this form/report; however, I
never tried it personally.