Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Text (Access XP)

    Seem to be having security type problems using the "Transfer Text" action in a macro.
    If the csv file I am trying to create exists, after saying Yes to delete it, it is deleted then recreated with zero size, then get the message "The Microsoft Jet database engine cannot open the file sanou.csv. It is already opened exclusively by another user, or you need permission to view its data."
    If the file does not exist, the file is created with zero size, then get the message "The Microsoft Jet database engine cannot open the file sanou.csv. It is already opened exclusively by another user, or you need permission to view its data."

    This used to work, but now will not work at work or on a laptop at home.

    Regards
    John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting Text (Access XP)

    I've found Access XP more stringent about filenames than even A2k. Try using a txt filename instead of csv and then rename the file afterwards if you want it to have a csv extension.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Text (Access XP)

    Just tried that but no difference.
    I am using an export specification to remove the quote marks from each text entry, if that adds any more information.
    The problem also occurs if I run the select query and then try to export it.
    Regards
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Text (Access XP)

    This would seem to be a problem with permissions in Windows rather than an Access problem. Check carefully that you have sufficient permissions in the folder where you want to save the text file.

    (Added
    I can export without problem to .csv or .txt, with or without quotes as text separator, with or without export specification, to a new file or overwriting an existing file. I'm using Access 2002 SP-2.

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Text (Access XP)

    Thanks Hans.
    I have administrator rights on my own laptop.
    Can create this as an Excel fileand works fine. Problem is getting it into the csv format I need after that.

    Have just seen your "added" part of the post. Two months ago, this worked. I would be blaming the txt converter except that it is the same result on 3 different machines, two on the company network with their own version of Access, and my own laptop loaded from a completely different source

    Nothing seems to make sense.

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Text (Access XP)

    I am very sorry, I have searched extensively, but I can't find anything that is relevant to your problem. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Exporting Text (Access XP)

    You indicate you can create an Excel file, but can you save the Excel file in the same folder you are trying to export the CSV text file to? You may not have permission set on that specific folder - assuming you are using an NTFS partition - even though you have administrator rights. If you can save the Excel file, then that is not the case, and it sounds as if you may have an office installation problem. Finally, if you can save the Excel file, it is straightforward to save an Excel file as a CSV text file.
    Wendell

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Text (Access XP)

    Thanks Hans for your efforts.
    Have (been forced to) done a complete reload of my laptop in the last 24 hrs. Windows 200 Pro , Office XP etc. Problem has not gone away. Possibly validates your efforts, Hans.
    Wendell, I can create the Excel file in the same folder. Both before and after the reload.
    The problem with going thru Excel is, and maybe this is a clue - the csv file has no headers in the first line and the first field is blank. So I create the file in Excel, save as csv then use notepad to delete the first line to get a csv file I can import into the ERP system. (The original objective). Have just tried eliminating the blank first column so the first column now contains data, but no difference. I think this paragraph is a red herring!
    Have noticed that I cannot delete the csv file thru explorer until I have closed Access. Even just closing the program is not sufficient, I have to get out of Access before I get control of the csv file to delete it. If I create an Excel file, I can delete it in explorer while my export query is still open????

    John

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Exporting Text (Access XP)

    John,
    It seems there are several things that seem wacky, which is often a sign of something completely unrelated going on. Can you export to a CSV file using the manual process? If so, that says the basic capability exists.
    As to the Excel export, if you are getting a blank column, that suggests your data source is blank, or you have specified a range in the Excel worksheet.
    As to deleting the CSV file through explorer, that is not surprising - but how did you create the CSV file to begin with? It sounds as though Access created it even though you got an error message.

    Is it possible that you have some weird data as your source, and that is causing the problem?
    Wendell

  10. #10
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Text (Access XP)

    Wendell
    Solved the problem. I had several fields which needed to be blank, so I gave them a null value. Seems csv does not like this. As soon as I changed them to "" - the export worked.
    Can someone please explain the difference between Null and "" in this situation?

    Many thanks

    John

    ORIGINAL POST - now unnecessary
    I would agree that something is wacky!!. Especially as this did work, and now it doesn't.
    The problem exists even if I use "File - export", with or without the "export specification".
    If I start with no csv file and export one, the file gets created, then the system says I cannot access it.
    With two different systems giving the same result (I'm thinking out load now) and having reloaded my laptop it can't be the program. I have recreated both the "program" and "data" .mdb by copying to a new database. So it must be the actual data.
    It would appear not to be the first blank column because if I eliminate that, I still get the problem. (The first column being blank is deliberate, because of the use the csv file is being put to afterwards. Importing to another system)
    I will try selecting a small amount of data with different parameters to see if it will work.

    Thanks

    John

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Exporting Text (Access XP)

    I think I'm a bit confused, but assuming that your problem is solved, the difference between Null and "" is that Null means the field has no value, or is essentially unknown, while the "" (which is referred to as an empty string), is a text field which has no characters, or in other words the Len() function would return 0. If you are having a problem with Null values, the best bet is to use the Nz() function to test for a Null in the field in a query, and replace it with suitable content when it is Null.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •