Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have been working for several days on the following problem and have not found a solution. I would appreciate any guidance you can provide.

    My department supports workstations with Windows XP SP3 and Office 2007 SP2. I support several Access databases that are still in the Office 2000 MDB format.

    We recently contracted for a Terminal Server which is running Windows Server 2008 R2 with Office 2007 SP2 (installed to C:\Program Files (x86)\). Users who travel will be accessing the databases via the Terminal Server while other users will be on their workstations.

    I have been testing with front-ends (MDBs) installed locally that link to tables in a back-end database (MDB) stored on a Novell server.

    When I open a local copy of the FE on the Terminal Server OR on my workstation, they work fine. If I open FEs on each of two separate workstations, they both work fine.

    When I have an FE open on the Terminal Server and then open the FE on the workstation (or vice versa – the order does not matter), the workstation FE produces error messages as soon as the code attempts to insert rows into a table or update data in a table. The most frequent message is “3027 - Cannot update. Database or object is read-only.” I have also seen “3073 - Operation must use an updateable query”. If I close the Terminal Server FE, the workstation FE immediately functions normally.

    I have tried without success -
    - Moving FE’s and BE to ACCDB format.
    - Moving FE’s to ACCDE format.
    - The original code I tested used a recordset to insert a record into a log table that has no relationships with other tables. I tried updating the code to run an SQL string instead.
    - We have a test Terminal Server with Office installed to C:\Program Files\. I have the same problems on the test box.

    The closest thing to a theory I have is that there is something different between how the Terminal Server FE and the workstation FE interact with the common back-end. The Terminal Server FE always works fine. It is the workstation FE that is always affected and can no longer update records in the linked tables.

    Thank you for any suggestions you have.

    Angela Griffin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    My guess is that the issue is the location of your BE MDB file. File locking and such on a Novell server are different from what Server 2008 does. Try moving the BE MDB file to a location on the Server 2008 machine and see if things work better. And I would suggest you look at upsizing it to SQL Server 2008 - it is a much more robust backend than an MDB file, and is much less likely to corrupt. The Express version of SQL Server 2008 is free and works very nicely with Windows Server 2008. We work in that environment with a number of clients at distant locations. But try moving the MDB first and see if my theory is correct. If that isn't the case, check the default open settings for databases on the Terminal Server and make sure they are opening databases in the shared mode.
    Wendell

  3. #3
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell -

    Thank you very much for your thoughts. Unfortunately, my options are limited for what I can try because I don't have any control over the server environment. The Terminal Server does not have file or printer sharing enabled because it is meant only to provide a remote desktop environment so our travelers can get to the files on the Novell server. I also don't have the option of upsizing to SQL Server 2008. We can try changing the Novell file rights, but I'm not sure this would do any good if the problem is that each environment treats file locking, etc. differently.

    I checked the settings for FEs and BE under Advanced options / Advanced section. They are all set to -

    Default open mode = Shared
    Default record locking = No locks
    Open databases by using record-level locking = Yes

    Thank you again. Although I cannot test your solution it would explain why none of the things I have tried have solved the problem.

    Angela Griffin

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well, given your options are limited that way, and each user has Access set to open in the Shared mode, not the Exclusive or the Read Only mode, then I would copy the front-end to each user profiles My Documents so each has their own copy. I have seen that issue cause trouble, though not exactly what you are seeing. And I would test the behavior by logging in at the server console and using the copy in each user's profile to make sure that database is being opened appropriately. Beyond that, I'm afraid you are stuck. I think the issue is the manner in which Server 2008 opens a database compared with how local workstations on the Novell network open files. One other thing you could try is to create a test workstation that uses Windows 7 and see if it can coexist with Server 2008 - it may well, as the two share much of the same networking and file access software.
    Wendell

  5. #5
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, Wendell. The information you have provided has been extremely helpful. We are going to move forward with testing a Windows 7 workstation and with possibly investigating alternative BEs (other than Access).

    Angela Griffin

Posting Permissions

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