Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to attach an existing SQL Server 2000 Database to SQL Server 2000 Desktop Engine

How to attach an existing SQL Server 2000 Database to SQL Server 2000 Desktop Engine

Moderate: Requires basic macro, coding, and interoperability skills.


This article applies only to a Microsoft Access project (.adp).


For a Microsoft Access 2000 version of this article, see 208888 .

↑ Back to the top

Summary

You may have a need to copy an existing database from Microsoft SQL Server 2000 and to place the database on another computer that is running Microsoft SQL Server 2000 Desktop Engine. This article describes how to do so. The process involves temporarily detaching the database from its server, copying the data file (.mdf) and log file (.ldf) to the target computer, and then attaching the files to the SQL Server 2000 Desktop Engine server there.



NOTE: The previous version of Microsoft SQL Server 2000 Desktop Engine is named Microsoft Data Engine (MSDE).

↑ Back to the top

More Information

The following steps demonstrate how to copy the sample pubs database from a computer that is running SQL Server 2000 and how to place it on a computer that is running SQL Server 2000 Desktop Engine:
  1. On the server that is running SQL Server 2000, open SQL Server Query Analyzer, and type the following line:
    sp_detach_db pubs
  2. On the Query menu, click Execute (or press F5) to run the query. Not that you receive a message that indicates that no records were returned.

    NOTE: Temporarily detaching the database from the server allows you to copy the data and log files.
  3. In Windows Explorer, copy Pubs.mdf and Pubs_log.ldf from the Microsoft SQL Server\MSSQL\Data folder on the server to the Microsoft SQL Server\MSSQL\Data folder on the SQL Server 2000 Desktop Engine computer
  4. To attach the files to the SQL Server computer again, run the following statements in the Query Analyzer. Your server's copy of SQL Server may be in a different location than this example; modify the path as needed.

        EXEC sp_attach_db @dbname = 'pubs',
            @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
            @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
  5. Use your copy of SQL Server 2000 Desktop Engine to open any working Access project.
  6. Click Queries, and then click New.
  7. In the New Query dialog box, click Create Text Stored Procedure, and then click OK.
  8. Type the following statements in the new procedure.

    NOTE: Some of the general stored procedure text may already be there. Also, your copy of SQL Server 2000 Desktop Engine may be on a drive other than drive C.

       Create Procedure AttachDB

       As

       EXEC sp_attach_db @dbname = 'pubs',

           @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
           @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

       return
  9. Save the stored procedure, and then close it.
  10. Double-click AttachDB to run it. Note that you receive a message that indicates that no records were returned. Click OK.
At this point, the pubs sample database is attached to your installation of SQL Server 2000 Desktop Engine, and you can create new Access projects from it.

↑ Back to the top

Keywords: kbfreshness2007, kboffice12yes, kbsweptsoltax, kbswept, kboffice2003yes, kbclientserver, kbhowto, kb

↑ Back to the top

Article Info
Article ID : 290623
Revision : 3
Created on : 4/18/2018
Published on : 4/19/2018
Exists online : False
Views : 178