locked
Microsoft Access 2007 RunSavedImportExport With Fixed Width Text File RRS feed

  • Question

  • I am wondering if anyone has come across this issue with Microsoft Access 2007 Service Pack 3.

    I have built an Access application using a split database.  I have a form with several buttons that each import a fixed width text file into a different backend database table.  I built the application on my workstation.  When I built the saved import, I used the wizard and pointed to a fixed width text file located in a directory on my hard drive.  I created and saved an import specification.  I saved the import.  I run the saved import from VBA code in the clicked event for the button on the form.  It works fine on my workstation but when I copied the application along with the text file to a different workstation, the import failed.  In debugging the accd file, I ran the saved import manually on the other workstation and I got a file not found error even though the file on the hard drive of the other machine and in the exact same directory structure as the machine from which the saved import was created.  I suspected that it was machine dependent and created a new saved import on my development machine but this time I put the text file for the import on a network drive.  In the wizard to create the saved import, I pointed to the text file on the network drive.  I saved this import.  I then copied the Access application to a different workstation and ran the saved import using the VBA code as before.  This time it worked!

    I suspect, but I can't prove it as I do not have Microsoft's source code for the RunSavedImport
    Export function, that when you create a saved import for a text file using a text file that is located on the hard drive, that the workstation name is saved along with the directory path to the file.  Then when you try to run the application from a different workstation, it can't find the import file because it is tied to a different workstation name.

    I am wondering if anyone in the Microsoft community knows if this is what is occurring and can verify it.  I'm thinking that it might have to be a Microsoft engineer or ex-Microsoft engineer who has worked on the Access product and would have access to the source code for Access.

    Wednesday, October 9, 2013 3:20 PM

All replies

  • I have found an answer to my original question about the workstation name being stored in the saved import as well as to what was happening to cause the initial error.  With the help of someone's post on a different forum which pointed me to look at the XML of the saved import.  What follows is what I found:

    I looked at the XML for the saved import specification and determined that the workstation name is NOT a part of the path. But I did notice something that helped me understand what the initial problem was. Here is the partial output of the XML for a saved import that was not working:

    : XML : "<?xml version="1.0" encoding="utf-8" ?>
    <ImportExportSpecification Path = "C:\ACCOUN~1\FinPro\DATA_F~2\IMPORT~1.CSV" xmlns="urn:www.microsoft.com/office/access/imexspec">
    "

    You will notice that the path for the import file is pointing to a file on my hard drive. You will also notice tildes in the path and file name. My original file name had dots in the filename, for example IMPORTB461ALMGMT.DTLML.DAT.20131001164553.csv. I had done two things to get it to work. First I replaced the dots in the filename with underscores but left the dot before the file extension .csv. I also moved it to a network drive.

    I had a hunch that removing the dots would be sufficient to fix the problem with creating the saved import with the file located on the hard drive. But that failed. So I then noticed the tildes in the path name of the XML and thought that the tildes might be the problem. So I edited the file name in the saved imports screen (in the Access GUI) removing the tildes and putting the full spelling of the path directory. I also left it pointing to the hard drive. This worked. Then I copied the application to a different machine and ran it successfully there.

    So now my application works on any machine when the source of the import file for the saved import is on the hard drive.

    Two questions remain unanswered and still puzzle me.

    1) Why it worked on my machine to begin with with the dots in the filename as well as the tildes in the path name.

    2) Where does Access store the XML for saved imports? I could not find it in any of the Access system tables. There were two that pertain to saved imports, namely MSysIMEXSpecs and MSysIMEXColumns. Neither of these tables contain the path of the import file or the XML. The only way that I was able to see the XML and the path of the import file was through the code:

    CurrentProject.ImportExportSpecification.xml

    I was able to find the path in the XML in the Watch window of the Access debugger.

    But where does Access store this information?

    Friday, October 11, 2013 4:15 PM