Difference between revisions of "Microsoft KB Archive/253435"

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
(One intermediate revision by the same user not shown)
Line 97: Line 97:
 
    
 
    
 
   'Create Objects
 
   'Create Objects
   Set sql = CreateObject("SQLDMO.SQLServer")
+
   Set sql = CreateObject("SQLDMO.SQLServer")
   Set db = CreateObject("SQLDMO.Database")
+
   Set db = CreateObject("SQLDMO.Database")
 
    
 
    
 
   'Connect to the server
 
   'Connect to the server
Line 104: Line 104:
 
    
 
    
 
   'Set the database object
 
   'Set the database object
   Set db = sql.Databases(strDatabase, "dbo")
+
   Set db = sql.Databases(strDatabase, "dbo")
 
        
 
        
 
   'Determine the location of database files, primary and log
 
   'Determine the location of database files, primary and log
Line 119: Line 119:
  
 
   'Copy database files to new names
 
   'Copy database files to new names
   FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
+
   FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
   FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
+
   FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
 
    
 
    
 
   'Re-attach original database
 
   'Re-attach original database
   sql.AttachDB strDatabase, strMDFfileName & "," & strLOGfile
+
   sql.AttachDB strDatabase, strMDFfileName & "," & strLOGfile
   sql.AttachDB strNewName, strMDFfilePath & strNewName & _
+
   sql.AttachDB strNewName, strMDFfilePath & strNewName & _
   ".mdf" & "," & strMDFfilePath & strNewName & "_log.ldf"
+
   ".mdf" & "," & strMDFfilePath & strNewName & "_log.ldf"
 
    
 
    
 
   'Set database object to the new database
 
   'Set database object to the new database
   Set db = sql.Databases(strNewName, "dbo")
+
   Set db = sql.Databases(strNewName, "dbo")
   Debug.Print "Database Created"
+
   Debug.Print "Database Created"
 
    
 
    
 
    
 
    
Line 140: Line 140:
 
CopySQLDB_Err:
 
CopySQLDB_Err:
 
   'Basic error handling.
 
   'Basic error handling.
   MsgBox Err.Description, vbInformation, "SQL OLE Automation"
+
   MsgBox Err.Description, vbInformation, "SQL OLE Automation"
 
   Resume CopySQLDB_End
 
   Resume CopySQLDB_End
  
Line 146: Line 146:
 
                         </pre></li>
 
                         </pre></li>
 
<li><p>Type the following line in the Immediate window, and then press ENTER:</p>
 
<li><p>Type the following line in the Immediate window, and then press ENTER:</p>
<pre class="codesample">?CopySQLDB(&quot;sa&quot;,&quot;&quot;,&quot;(local)&quot;,&quot;NorthwindCS&quot;,&quot;NwindCS&quot;)
+
<pre class="codesample">?CopySQLDB("sa","","(local)","NorthwindCS","NwindCS")
 
                         </pre>
 
                         </pre>
<p>'''NOTE:''' This example works in most cases. If your server is not local, type the name of the server to which you are connecting instead of the keyword &quot;(local)&quot; in the line above. In addition, this example presumes that sa is logging on with no password. You can also log on with another account that has similar permissions.<br />
+
<p>'''NOTE:''' This example works in most cases. If your server is not local, type the name of the server to which you are connecting instead of the keyword "(local)" in the line above. In addition, this example presumes that sa is logging on with no password. You can also log on with another account that has similar permissions.<br />
 
<br />
 
<br />
 
If successful, you see '''Database Created''' in the Immediate window.<br />
 
If successful, you see '''Database Created''' in the Immediate window.<br />

Latest revision as of 13:53, 21 July 2020

Knowledge Base


ACC2000: How to Use SQL-DMO to Copy an Existing MSDE or SQL Server Database to a New Database

Article ID: 253435

Article Last Modified on 1/26/2005



APPLIES TO

  • Microsoft Access 2000 Standard Edition



This article was previously published under Q253435

Advanced: Requires expert coding, interoperability, and multiuser skills.

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


SUMMARY

This article demonstrates how to create a custom Visual Basic for Applications function in Microsoft Access that you can use to copy an existing Microsoft Data Engine (MSDE) or a Microsoft SQL Server database.

MORE INFORMATION

In order to copy an MSDE database or a Microsoft SQL Server database, the database files must first be detached. Then the files must be copied. Finally, the original database and the new database must be reattached to the server.

The following steps involve creating a new copy of the sample Access project NorthwindCS. To follow these steps, you must have access to a computer running either MSDE or SQL Server 7.0 that has a copy of the sample database NorthwindCS installed.

  1. Create a new Access database.
  2. In the Database window, click Modules, and then click New.
  3. On the Tools menu, click References.
  4. Click to select the Microsoft SQLDMO Object Library check box.
  5. Type the following line in the Declarations section if it is not already there:

    Option Explicit
                        
  6. Type the following procedure:

     
    
    Function CopySQLDB(strLogin As String, strPwd As String, _
    strSrv As String, strDatabase As String, strNewName As String)
    
    '===============================================================
    ' Parameters
    ' --------------------------------------------------------------
    ' strLogin:  Name of the Login account used.
    ' strPwd:  The Password for the login account.
    ' strSrv:  The server to which you are connecting.
    ' strDataBase:  The name of the database you want to copy.
    ' strNewName: Desired name of the new copy of the database.
    '===============================================================
    
       Dim sql As Object
       Dim db As Object
       
       Dim strMDFfilePath As String
       Dim strMDFfileName As String
       Dim strLOGfile As String
          
       On Error GoTo CopySQLDB_Err
       
       'Create Objects
       Set sql = CreateObject("SQLDMO.SQLServer")
       Set db = CreateObject("SQLDMO.Database")
       
       'Connect to the server
       sql.Connect strSrv, strLogin, strPwd
       
       'Set the database object
       Set db = sql.Databases(strDatabase, "dbo")
          
       'Determine the location of database files, primary and log
       strMDFfilePath = db.PrimaryFilePath
       strMDFfileName = _
       Trim(db.FileGroups.Item(1).DBFiles.Item(1).PhysicalName)
       strLOGfile = Trim(db.TransactionLog.LogFiles(1).PhysicalName)
    
       'Clear out the database object
       Set db = Nothing
       
       'Detach database to allow copying
       sql.DetachDB (strDatabase)
    
       'Copy database files to new names
       FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
       FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
       
       'Re-attach original database
       sql.AttachDB strDatabase, strMDFfileName & "," & strLOGfile
       sql.AttachDB strNewName, strMDFfilePath & strNewName & _
       ".mdf" & "," & strMDFfilePath & strNewName & "_log.ldf"
       
       'Set database object to the new database
       Set db = sql.Databases(strNewName, "dbo")
       Debug.Print "Database Created"
       
       
    CopySQLDB_End:
       'Clear out the objects before ending
       Set db = Nothing
       Set sql = Nothing
       Exit Function
                
    CopySQLDB_Err:
       'Basic error handling.
       MsgBox Err.Description, vbInformation, "SQL OLE Automation"
       Resume CopySQLDB_End
    
    End Function
                            
  7. Type the following line in the Immediate window, and then press ENTER:

    ?CopySQLDB("sa","","(local)","NorthwindCS","NwindCS")
                            

    NOTE: This example works in most cases. If your server is not local, type the name of the server to which you are connecting instead of the keyword "(local)" in the line above. In addition, this example presumes that sa is logging on with no password. You can also log on with another account that has similar permissions.

    If successful, you see Database Created in the Immediate window.

    At this point, a copy of the NorthwindCS SQL server database has been created with the name NwindCS.



Additional query words: pra

Keywords: kbhowto kbfix kbclientserver KB253435