ExportXML in MS Access 2007 vba (1 Viewer)

[Sigma248]

Can anyone tell me what is wrong with the ExportXML command in the following code. It returns the error:

Run time error ’2467’:
The expression you entered refers to an object that is closed or does not exist.

vba Code:

Private Sub cmdSave_Export_Click()

Dim intID As Long
Dim strQuery As String
Dim strOutputPath As String
Dim strFileName As String
Dim strFileNameAndPath As String

DoCmd.Save

Set rstWorkOrder = CurrentDb.OpenRecordset("SELECT WorkOrder.ID FROM WorkOrder")

rstWorkOrder.MoveLast

intID = rstWorkOrder!ID

strQuery = "SELECT * FROM WorkOrder WHERE ID=" & intID

strOutputPath = "C:\Users\Dean\My Documents\"

strFileName = "Job Entry Form.xml"

strFileNameAndPath = strOutputPath & strFileName

ExportXML objecttype:=acExportQuery, DataSource:=strQuery, datatarget:=strFileNameAndPath

MsgBox ("Exported Work Order to C:\Users\Dean\My Documents\Job Entry Form.xml")

End Sub

 

[DCrake]

The reason why this does not work is that you are trying to pass a sql string to the ExportXML as the query source. The query must be part of the QueryDefs collection.

To overcome this you need to be looking at changing the sql statement of a known query using querydef.sql = .....

 

[Sigma248]

Hi,

I have made the changes suggested but still get the same error.

Code:

Private Sub cmdSave_Export_Click()
Dim intID As Long
Dim strQuery As String
Dim myQueryDef As QueryDef
Dim strOutputPath As String
Dim strFileName As String
Dim strFileNameAndPath As String
DoCmd.Save
Set rstWorkOrder = CurrentDb.OpenRecordset("SELECT WorkOrder.ID FROM WorkOrder")
rstWorkOrder.MoveLast
intID = rstWorkOrder!ID
strQuery = "SELECT WorkOrder.* FROM WorkOrder WHERE ID=" & intID
Set myQueryDef = CurrentDb.CreateQueryDef("", strQuery)
strOutputPath = "C:\Users\Dean\My Documents\"
strFileName = "Job Entry Form.xml"
strFileNameAndPath = strOutputPath & strFileName
Application.ExportXML objecttype:=acExportQuery,
DataSource:="myQueryDef", datatarget:=strFileNameAndPath
MsgBox ("Exported Work Order to C:\Users\Dean\My Documents\Job Entry Form.xml")
End Sub


Any suggestions.

Dean.

 

[DCrake]

Your export xml line should read

ExportXML acExportQuery, "NameOfQueryHere", strFileNameAndPath

Sample of resetting the SQL

Dim qryTest As QueryDef
Dim StrTest As String

StrTest = "SELECT Max(tblWorkflowActions.ACTIONNO) AS MaxOfACTIONNO, tblWorkflowActions.WORKNO " & _
"FROM tblWorkflowActions " & _
"GROUP BY tblWorkflowActions.WORKNO & _ 
"HAVING (((Max(tblWorkflowActions.ACTIONDATE))< #" & [YourCellRangeHere] & #)); "


Set dbs = Opendatabase("Z:\YourMDB.MDB")
Set qryTest = dbs.QueryDefs("QryWorkflowActions")
qryTest.SQL = StrTest

This code takes an existing query and replaces the current SQL with the revised SQL. If you use your method (CreateQueryDef) Acces may not have addited it to your collection by the time you want to employ it further dwon in your code.