This code shows to use to detach a database using SQL-DMO and also how to create a script to reattach the database. See comments in the code!

Sub DetachDB(ServerName As String, DBNamePattern As String, UpdateStatisticsFirst As Boolean)

'3/25/01 Andy Warren
' Shows how to detach a database using DMO and how to generate
'the reattachment script. DBNamePattern can the name
'of a database or a pattern using standard "VB" syntax (use * for
'wildcard, not %). Because sp_attach_db only supports up to 16 files
'and doesnt support filegroups, I've opted to always use the create
'database for attach method instead. If you're using SQL 2000 you
'should consider using the Copy Database Wizard. This code does not
'address SQL logins that may need to be moved
'Requires reference to MS SQL-DMO library plus the MS Scripting Runtime

Dim oServer As SQLDMO.SQLServer
Dim oDB As SQLDMO.Database
Dim vResult As String
Dim fso As FileSystemObject
Dim tsAttach As TextStream
Dim sFileName As String
Dim oDBFile As SQLDMO.DBFile
Dim oLogFile As SQLDMO.LogFile
Dim oGroup As SQLDMO.FileGroup
Dim iCount As Integer
Dim oFiles As Collection
Dim vFile As Variant
Dim sGrowthIncrement As String
Dim oUser As SQLDMO.User
Dim oLogin As SQLDMO.Login

On Error GoTo Handler

'simple err checking
If ServerName = "" Or DBNamePattern = "" Then
MsgBox "You MUST provide the server name and a database name/pattern", vbInformation + vbOKOnly, "Error"
Exit Sub
End If

'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With

'need this for file access
Set fso = New FileSystemObject

'loop through database collection to find ones that match our
For Each oDB In oServer.Databases

'see if this db matches what we want to detach
If UCase(oDB.Name) Like UCase(DBNamePattern) Then

'default to the root, but could the the app.path
'property or hardcod differently, we're creating
'one file per db
sFileName = "C:\AttachDB_" & oDB.Name & ".sql"

'open and overwrite any previous file
Set tsAttach = fso.CreateTextFile(sFileName, True, False)

'put some header comments in
tsAttach.Write "--" & Format$(Now, "mmm dd, yyyy")
tsAttach.WriteLine "--Script to attach/create database " & oDB.Name
tsAttach.WriteBlankLines 1

'switch to master
tsAttach.WriteLine "Use Master"
tsAttach.WriteLine "GO"
tsAttach.WriteBlankLines 1

'build create db, always have to have a Primary file group
tsAttach.WriteLine "Create Database " & oDB.Name & " on PRIMARY "

'process the data files first, could be multiple filegroups
For Each oGroup In oDB.FileGroups
'reset on each pass
iCount = 0
For Each oDBFile In oGroup.DBFiles
'have to count each file so we know when we are at the end
iCount = iCount + 1

'the only tricky part here is deciding whether we need a comma
'at the end of each pass
With tsAttach
.WriteLine "("
.WriteLine "Name='" & oDBFile.Name & "',"
.WriteLine "FileName='" & RTrim$(oDBFile.PhysicalName) & "',"
.WriteLine "Size=" & oDBFile.Size & ","
'DMO returns a -1 if set to unlimited growth
If oDBFile.MaximumSize <> -1 Then
.WriteLine "(MaxSize=" & oDBFile.MaximumSize & ","
End If
If oDBFile.FileGrowthType = SQLDMOGrowth_MB Then
sGrowthIncrement = "MB"
sGrowthIncrement = "%"
End If
.WriteLine "FileGrowth=" & oDBFile.FileGrowth & sGrowthIncrement
If iCount = oGroup.DBFiles.Count Then
.WriteLine ")"
.WriteLine "),"
End If
End With

'its possible to have a filegroup with no files, and we script will fail
'if we try to add another filegroup called Primary
If oGroup.DBFiles.Count > 0 Then
If oGroup.Name <> "PRIMARY" Then
tsAttach.WriteLine "FileGroup " & oGroup.Name
End If
End If

tsAttach.WriteLine "Log on"

'reset counter
iCount = 0
For Each oLogFile In oDB.TransactionLog.LogFiles
iCount = iCount + 1
With tsAttach
.WriteLine "("
.WriteLine "Name='" & oLogFile.Name & "',"
.WriteLine "FileName='" & RTrim$(oLogFile.PhysicalName) & "',"
.WriteLine "Size=" & oLogFile.Size & ","
If oLogFile.MaximumSize <> -1 Then
.WriteLine "MaxSize=" & oLogFile.MaximumSize & ","
End If
If oLogFile.FileGrowthType = SQLDMOGrowth_MB Then
sGrowthIncrement = "MB"
sGrowthIncrement = "%"
End If
.WriteLine "FileGrowth=" & oLogFile.FileGrowth & sGrowthIncrement
If iCount < oDB.TransactionLog.LogFiles.Count Then
.WriteLine "),"
.WriteLine ")"
End If
End With

'have to add this!
tsAttach.WriteLine "For Attach"

'run each as a separate batch
tsAttach.WriteLine "GO"

'close the object
Set tsAttach = Nothing

'detach it - this method returns a string, but we dont need it
vResult = oServer.DetachDB(oDB.Name, True)
End If

'standard clean up
On Error Resume Next
Set fso = Nothing
Set oServer = Nothing

Exit Sub

If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo, "InstallDB") = vbYes Then
Resume Next
End If

End Sub