After restore MSSQL Database by VB program, the database cannot login again.

Posted on 2005-03-10
Medium Priority
Last Modified: 2010-05-02
My VB Program contains a Restore database function :

Public Function ExecuteSQLOK(CN As ADODB.Connection, SQL As String, Optional UseTransaction As Boolean = False) As Boolean

' Execute an SQL statement
On Error Resume Next
If UseTransaction Then CN.BeginTrans
CN.Execute SQL, , adCmdText + adExecuteNoRecords

If UseTransaction Then CN.CommitTrans

If Err.Number <> 0 Then
    If UseTransaction Then
    End If
    ExecuteSQLOK = False

    ExecuteSQLOK = True
End If

End Function

Public Function RestoreDBOK(CN As ADODB.Connection, DatabaseName As String, SourceFile As String) As Boolean

' Restore a database don't try master

Dim SQL As String
Dim OK

SQL = "USE master" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'TRUE'" + vbCrLf
SQL = SQL + "RESTORE DATABASE " + DatabaseName + " FROM DISK = '" + SourceFile + "'" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'FALSE'" + vbCrLf

RestoreDatabaseOK = ExecuteSQLOK(CN, SQL)

End Function

However, after restore the database. I cannot connect to this database again.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'TABLENAME'. Login fails.

Please help~~~

Thank you.
Question by:morye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment

Accepted Solution

elmer88techteam earned 200 total points
ID: 13514475

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question