Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208888 - Last Review: June 28, 2004 - Revision: 2.0
ACC2000: How to Attach Existing SQL Server 7.0 Database Files to Microsoft Data Engine (MSDE)
This article was previously published under Q208888
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access project (.adp).
You may have a need to copy an existing database from Microsoft SQL Server 7.0 and to place the database on another computer that is running Microsoft Data Engine (MSDE). This article describes how to do so. To summarize, 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 MSDE server there.
The following steps demonstrate how to copy the sample pubs database from a computer running SQL Server 7.0 and how to place it on a computer running MSDE:
-
On the server running SQL Server 7.0, open SQL Server Query Analyzer, and type the following line:
sp_detach_db pubs
-
On the Query menu, click Execute (or press F5) to run the query. Not that you receive a message indicating that no records were returned.
NOTE: Temporarily detaching the database from the server allows you to copy the data and log files.
-
In Windows Explorer, copy Pubs.mdf and Pubs_log.ldf from the mssql7\data folder on the server to the mssql7\data folder on the MSDE computer.
-
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:\mssql7\data\pubs.mdf',
@filename2 = 'c:\mssql7\data\pubs_log.ldf'
- Click Stored Procedures, and then click New.
- Type the following statements in the new procedure.
NOTE: Some of the general stored procedure text may already be there.
Also, your copy of MSDE may be on a drive other than drive C.
Create Procedure AttachDB
As
EXEC sp_attach_db @dbname = 'pubs',
@filename1 = 'c:\mssql7\data\pubs.mdf',
@filename2 = 'c:\mssql7\data\pubs_log.ldf'
return
- Save the stored procedure, and then close it.
- Double-click AttachDB to run it. Note that you receive a message indicating that no records were returned. Click OK.
At this point, the pubs sample database is attached to your installation of MSDE, and you can create new Access projects from it.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate