How can I change the path of a saved export from a form in Access using VBA?

Question

I'm using 'Saved exports' to update an application in Access. Until recent it has been working fine because every user was using the same location. Since Windows 8, this location is protected and some have relocated the target file. An input box asks for the path to this new location. Now I'm looking for a way to introduce this path into the Saved Export or a VBA-way to export forms and queries to the target file. My research showed me a lot of export of data, but never the export of components.

Answer 1

If you really want to continue using Saved Exports to transfer the database objects then you'll have to tweak the XML in the ImportExportSpecification object as described in the answers to the question here:

How to specify a different file path for a saved Excel import

However, that approach offers little benefit in your case because you don't need the extra features of a Saved Import/Export (column mapping, date formats, character sets, etc.). It would be much more straightforward to use the VBA DoCmd.TransferDatabase method like this

targetDbSpec = "C:\Users\Public\SomeOtherDb.accdb"
DoCmd.TransferDatabase _
        TransferType:=acExport, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:=targetDbSpec, _
        ObjectType:=acQuery, _
        Source:="ClientQuery", _
        Destination:="ExportedQuery"