ACC: Debugging and Troubleshooting Tips for Attached SQL Tables (102437)



The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97

This article was previously published under Q102437
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article lists some techniques that you can use to determine indirect causes of error messages when using linked (attached) SQL tables:
  1. Make sure the connection information such as server name, login ID, and password, is correct. If any of this information has changed, you may have to delete and then re-create the links with the new connection information.
  2. Check for multiple, corrupted, or outdated copies of the following files.

    In Microsoft Windows versions earlier than Windows 95, use File Manager to search for copies of the following files:

    ODBC.DLL - dated 10-16-92 or later, with 44736 bytes
    DBNMP3.DLL - dated 9-15-92 or later, with 8241 bytes
    SQLSRVR.DLL - dated 10-16-92 or later, with 135792 bytes
    NETAPI.DLL - depends on the network being used
    COMMDLG.DLL - dated 10-25-92 or later, with 89248 bytes

    NOTE: The sizes and dates referenced are correct if the files were installed by Microsoft Access versions 1.0 or 2.0.

    Use Windows Explorer in Microsoft Windows 95, Find Files or Folders in Windows 98, or Search For Files or Folders in Windows 2000, to search for copies of the following files:

    ODBC32.DLL - dated 9-28-95 or later, with 63000 bytes
    DBNMPNTW.DLL - dated 9-28-95 or later, with 17000 bytes
    SQLSRV32.DLL - dated 9-28-95 or later, with 207000 bytes
    NETAPI32.DLL and NETAPI - depends on the network being used

    NOTE: The sizes and dates referenced are correct if the files were installed by Microsoft Access 7.0 or 97.
  3. Make sure that all the files listed in Step 1 are in the appropriate locations. All of the files are usually installed in the Windows System folder.
  4. Use another application, such as one of the four listed below, on the same computer and try to link the same data source:

    • Q+E (comes with Microsoft Excel)
    • SQL Server Administration Facility (SAF)
    • PowerBuilder
    • MSQuery
    If you cannot access the data source from another application, you may have a problem with the network, not with Microsoft Access or the Microsoft Open Database Connectivity (ODBC) driver.
  5. Try to link another data source, such as another SQL Server table, from a different database server. If you can link the other data source, the original server may be unavailable, or you may need to reconfigure the entry for that server by using the ODBC Administration utility.
  6. Try dropping the existing links to the tables and relinking the tables. If the configuration of the existing server has changed, the current connection string or the linked table properties may no longer be valid.
  7. Try to link the same data source from a different computer. If you still cannot gain access to the SQL Server computer, you may have a network-wide problem, or perhaps the server is down or not communicating.
  8. Try increasing the ODBC time-out settings in the [ODBC] section of your MSACCESS.INI file for Microsoft Access 1.0 and 1.1 (or the MSACC20.INI file for Microsoft Access 2.0).

    NOTE: For Microsoft Access 7.0 and 97, try increasing the ODBCTimeout setting on the query's property sheet.
  9. For Microsoft Access 2.0 or earlier, rename the current ODBC.DLL, DBNMP3.DLL, and SQLSRVR.DLL files, restart the server, and reinstall ODBC.

    NOTE: For Microsoft Access 7.0 and 97, rename the current ODBC32.DLL, DBNMPNTW.DLL, and SQLSRV32.DLL files, restart the server, and reinstall ODBC.

REFERENCES

For more information about installing Microsoft Access 97 on a network, see the Network Readme file, Netwrk8.txt, located on the compact disc in the Office folder. For additional information about network installations, you can obtain a copy of the Microsoft Office 97 Resource Kit, which is available from Microsoft Press. To order the Microsoft Office 97 Resource Kit (ISBN: 1- 57231-329-3), call (800) MS-PRESS in the United States or (800) 667-1115 in Canada. For more information, visit Microsoft Press Online at the following Web address:

http://mspress.microsoft.com/

PowerBuilder is manufactured by Powersoft Corporation, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbhowto kbinterop kbusage KB102437