Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Access on Server Network (Access 2003)

    We are in the process of upgrading a database from Access 97 to 2003 and moving from a peer-to-peer network to a server based network. The 2003 version of the database seems to be working fine but we have had a problem with the back-end file on the server. One of our VBA routines executes a DoCmd.TransferText that is supposed to write a query's data into a file on the workstation at Cata.txt. That operation fails with an error message about not being able to open the file. In this test, we had the front-end file also stored on the server and were opening it from the workstation. I think we actually should have a copy of the front-end on each workstation but I don't think that would have caused this problem. BTW, does everyone agree that we really should put a copy of the front-end on each workstation?

    Does anyone have any idea why the TransferText couldn't open the file? In the environment that I described wouldn't the VBA code be operating in the workstation environment where the existence of the new network and server would not be related to this problem?

    Thanks,
    Bill

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

    Re: Access on Server Network (Access 2003)

    1) Giving each user an individual copy of the frontend improves performance and prevents a lot of problems.

    2) It shouldn't matter where the frontend or backend is stored when you export to a text file on C:, as long as the user has sufficient permissions to do so. Are you sure that the user is allowed to write to the root of C: ? In some environments, "standard" users do not have permission to create files in the root of C:.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Access on Server Network (Access 2003)

    Hans,

    We will go ahead and put a copy of the front-end on each workstation. We believe the user had all the permissions that are needed to write to that file so we will have to investigate further. You have helped verify my impression that the nature of the network and the permissions on the network and the server shouldn't have anything to do with the failure to write that file.

    Thanks,
    Bill

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access on Server Network (Access 2003)

    When you specify the "C:" drive in Access, it is relative to the workstation not the location of the frontend/backend. That aside, I believe that all of us here agree that you need to move the frontend to the workstations.

    Try using Notepad to create a little file and save it to C:data.txt. This will tell you if you've got permissions issues. Also make sure you don't have a directory named "data.txt" on C:! BTW, what is that line of code you are using to write that file?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Access on Server Network (Access 2003)

    Mark,

    Nice to see you over here too! Yes, I believe we all agree on where each front-end file should be located. This database had been running in its Access 97 and Access 2000 versions for quite a few years now and the Habitat office is in the process of upgrading to Office 2003 and adding a server to the network. The database converted without any complaints but this problem and some similar ones have shown up as the server is being put into use. I've actually moved away and another guy is doing the work so I don't know exactly what has been tried and what hasn't. Writing that simple file to the C: drive is certainly a good idea. After posting my messages it occurred to me that maybe the problem was in the Kill statement ahead of the TransferText and we have verified that the old version of the output file is still on the drive so the Kill has failed and that may be the reason for the TransferText to fail.

    The Access 97 code that is involved is this:

    'get ready to output the resulting data
    FileName = "C:PeopleData.txt"
    On Error Resume Next
    Kill FileName
    Kill "C:schema.ini"
    On Error GoTo 0
    'output the resulting data to a temporary file
    DoCmd.TransferText acExportMerge, , "MailMergeForPeople Final varSQL", FileName, True

    I imagine there is some basic problem with permissions or something like that that is causing the problems. So many changes from the previous "normal" were mixed up in the test that failed so I've been trying to sort out where the problem really might be located. I think some more simpler tests have to be tried now and I don't think that will happen for another day or two.

    Thanks for you help.

    Bill

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access on Server Network (Access 2003)

    I think it is your use of "acExportMerge"; this would indicate you are trying to merge your data into an existing file. I'd use "acExportDelim", assuming you wanted a delimited text file.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Access on Server Network (Access 2003)

    I don't think acExportMerge means that Access will try to merge the export with an existing file; it means that it will use a format that is suitable for use as a source for mail merge in Word, corresponding to the "Microsoft Word mail merge (*.txt)" option when exporting manually.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Access on Server Network (Access 2003)

    Well, that points out one of the frustrations about Access -- I haven't been able to find any explanation about those constants (and many others) as to when to use one versus another. The output file will be used as the data source for a mail merge in Word and the acExportMerge constant has worked correctly for us for several years. Of course, Access 2003 may have interpreted those more carefully than Access 97 and make a difference here. I remember that I did add the Kill statement to solve a problem after the original implementation and maybe that was because it was appending to the previous data. We can try changing that constant but that wouldn't explain why the Kill did not work. We will have to run it again without the On Error Resume Next to see what happens when the Kill is executed.

    Thanks,
    Bill

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access on Server Network (Access 2003)

    That may be, although I don't know how that differs from acExportDelim with HasFieldNames=True (which is what I normally use when exporting for Word). Maybe it automatically turns on the HasFieldNames?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Access on Server Network (Access 2003)

    Well, that is the way we are using the exported data and it has worked for a long time so I agree that I don't think that is the cause of the present problem.

    Thanks,
    Bill

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

    Re: Access on Server Network (Access 2003)

    I don't know, I always use mail merge based on a table or query in Access itself, not on an exported text file.

Posting Permissions

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