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?

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

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Petr Jankovsky" <Petr (E-Mail Removed)> wrote in message
news:79726FD0-B129-4B2D-B040-(E-Mail Removed)...
> 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?

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

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:(E-Mail Removed)...
> 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
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Petr Jankovsky" <Petr (E-Mail Removed)> wrote in
> message news:79726FD0-B129-4B2D-B040-(E-Mail Removed)...
>> 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?
>
>

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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:(E-Mail Removed)...
> 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
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Petr Jankovsky" <Petr (E-Mail Removed)> wrote in
> message news:79726FD0-B129-4B2D-B040-(E-Mail Removed)...
>> 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?
>
>

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Petr Jankovský" <(E-Mail Removed)> wrote in message
news:06D592C6-95C3-4183-839D-(E-Mail Removed)...
> 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" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:(E-Mail Removed)...
>> 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
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Petr Jankovsky" <Petr (E-Mail Removed)> wrote in
>> message news:79726FD0-B129-4B2D-B040-(E-Mail Removed)...
>>> 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?
>>
>>
>