Modified 2001/06/19 - JSB

MS ACCESS 2000 - Hazards and Issues

New: When Converting from Access 97

  • The usual "Provider error '80004005' - Unspecified error" will appear when accessing an Access 2000 DB, freshly converted from Access 97, through a browser unless you have granted full IUSR priviliges on the actual DB file. (Apply the same steps described in item 1. below to the to the .mdb file itself.)

    0. DB Programming, Internal Design Standards Practice

  • We should adopt, at least as an internal standard, and perhaps as an ODBM standard, the use of nothing smaller than a Long Integer in Access DB designs. I suggest this as a standard because it is possible that any numeric table field could be used in a Join, and, thus would be required to be a Long Integer.

    I. Problems with Setting an OBJdbConnection Object from VBScript

    Take nothing for granted upon the first time usage of an Access 2000 DB. The scenerio I had was that I migrated an Access 97 DB, which previously functioned with serverside VBScript connectivity, to Access 2000 format. (Access 2000 was newly installed at this point.) The .asp file test yeilded the following 500 class error from Access 2000 to the Response.Object:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    [Microsoft] [ODBC Microsoft Access Driver] General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x8cc Thread 0x94c DBC 0x2e361ec Jet'.

    The .asp code where it choked is:

    Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    OBJdbConnection.Open("Driver={Microsoft Access Driver(*.mdb)};DBQ=" & orgPath & "\whrereEverYourAppIs\yourDB.mdb")

    1. Set Windows Temp Directory Priviliges Appropriately (for IUSR)

    1. Open the Windows (file) Explorer.
    2. Find the \WINNT\Temp directory.
    3. Right click and select "Temp Properties".
    4. In the resulting panel click on the "Security" tab.
    5. Now click the "Add" button.
    6. In the new "Select Users ..." panel (the top list box) select the user entity whose first 5 characters are "IUSR_", which is the Internet Guest User.
    7. (After adding the IUSR you will be back in the "Security" tab in "Temp Properties" panel.)
    8. In the "Permissions" box allow only List Folder Contents, Read, and Write permissions.
    9. It also very important to click the checkbox saying "Allow inheritable from parent to propagate this object".

    2. Set Server Application Directory Priviliges Appropriately (for IUSR)

  • All the steps to do this are the same as in the above paragraph number 1.

    NB: the "Server Application Directory" refers to the area below the server-root, which is the physical directory from whence the server serves. In MS Server 2000 case this is C:\Inetpub\wwwroot\whrereEverYourAppIs , to which one would browse with the URL, http://yourHost/whrereEverYourAppIs .

    Back to top of page.

    II. Problems with timing out during a SQL query from the OBJdbConnection Object in VBScript

    After fixing the problem with setting the OBJdbConnection Object (I., above), I still kept getting another error:

    Provider error '80004005' Unspecified error xxx.asp, line ###

    Though none of this is shown in the error message, yhis involved an assumed timeout of the OBJdbConnection Object while accessing the Access 2000 DB. The fix for this is merely to set a timeout value for the OBJdbConnection Object:

    Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    ' Without the following ConnectionTimeout setting application fails intermittently.
    OBJdbConnection.ConnectionTimeout = 20
    OBJdbConnection.Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=" & orgPath & "\whrereEverYourAppIs\yourDB.mdb")

    Back to top of page.

    III. Entry from Bob Doyle skyBug #223:

    Trying to get Access 2000 working on skyBuilders at Interland: Access 2000 (apparently) installs a new Jet engine, which requires different permissions on various Temp folders.

    Solution: Give the IUSR read and write permissions on the Temp directory (in WINNT) used by the Jet engine to write its .ldb files.

    Note that when an Access 97 database is converted to Access 2000, the IUSR does not have permissions on the new file. When we added the IUSR, it came up with Read and with Read and Execute permissions. We had to uncheck Read and Execute, and check Write. Then everything worked.
    __________________________________
    Symptoms:

    Several database connection errors;
    Name not valid problem;
    Needed .xml in Application Mappings (to .asp).

    [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key
    'Temporary (volatile) Jet DSN for process 0x544 Thread 0x4c0 DBC 0x14e02024 Jet'.

    /timelines/db.inc, line 56

    Reset permissions on all files to allow IUSR?
    Propagated inherited permissions. (Missed getting Write permission on 1st pass.)

    Provider error '80004005'
    Unspecified error

    /timelines/db.inc, line 56

    Database does not have IUSR access?
    Also IUSR permissions?
    Maybe TEMP problem? for .ldb?
    Access 2000 stores its .ldb in a different place from 97?
    TEMP, and WINNT/Temp?
    __________________________________
    Tried to change back to the old Access 97 .mdb (skyBuilders.98.mdb), renaming it to timeLines .mdb (and new timeLines.mdb to timeLines2000.mdb)to work with the new db.inc.
    but...

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key
    'Temporary (volatile) Jet DSN for process 0x544 Thread 0x688 DBC 0x23debbc Jet'.

    /timelines/db.inc, line 56

    Maybe installing Access 2000 changed the jet engine? (But skyNT97 is running under Windows 2000?)
    __________________________________
    No, it was a permissions problem. IUSR lacked write privileges (had Read and Execute? fixed this) Finally, change dbJS.inc to timeLines.mdb (from skyBuilders.98.mdb)

    Back to top of page.

    IV. Miscellaneous notes related to ODBC for MySQL and MS Windows

  • Access 2000 could need a patch from Microsoft to be able to export tables to MySQL; Just download and install the newest Microsoft MDAC from http://www.microsoft.com/data .

    V. General Access 2000 Information Note (re: Schema TABLES)

  • When accessing DB Schema tables please make sure to only retrieve TABLE information, not SYSTEM TABLE, VIEW, etc. In your ASP code make sure to select info based on the condition "= TABLE" being satisfied.

    VI. General Access 2000 Information Note (re: SYSTEM TABLES)

    This is just information, not a warning. There should be no real reason for even a DB administrator to need to manipulate, or even read Access system tables. If you are doing things with tables of type "SYSTEM TABLES", i.e., DBSchema sytem manipulators, the following tables will need permissions adjustments: MSysAccessObjects, MSysACEs, MSysQueries, and MSysRelationships.

  • First, open the database you want to use.
  • From the main menu, click on Tools>Options>View.
  • In the "View" panel of the "Options" window check the checkboxes labelled, "Hidden objects" and "System objects".

  • Next you may want to enable read access each of these system tables.
  • Highlight one of the newly visible system tables, eg., MSysACEs.
  • From the main menu, click on Tools>Security>User and Group Permissions.
  • In the resulting "User and Group Permissions" panel in the "Permissions" tab check the "Read design" and "Read data" checkboxes.

    Back to top of page.

    < Persistent Archive  
    Edit 
    Language: fr  | it  | de  | es  | pt  | ar  | he  | da  | nl  | zh  | ja  | ko  | none 
    skyCalendar

    This Version:
    Archived at: https://www.skybuilders.com/Users/Jesse/Docs/Access2000.20010523125312.html

    Requests
     Version: 58091 | Series: 85903