krishnamo
01-20-2013, 08:42 PM
Hi Guys,
I am new to this forum. Need a little bit of help.
I am trying to run a piece of code when i run the SQL Script it run well. But when i run the same script via VBA it throws the following error.
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147217871 (80040e31)':
Automation error
---------------------------
OK Help
---------------------------
Private Sub CommandButton4_Click()
On Error GoTo Erred
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.DisplayPageBreaks = False
Dim Qry As String
Dim i As Integer
Dim Filename As String
Dim BkupDbname As String
Sheet6.Db_name = "master"
If Sheet2.cnn1.State <> adStateOpen Then
Sheet2.connect2
End If
Set rs = Nothing
If Sheet2.checkerr2 = 1 Then
GoTo Last:
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox2.Value = False Then
MsgBox "Please Atleast One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox2.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox2.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If ComboBox1.Value = "" Then
MsgBox "Please Select the Server Name!!"
GoTo Last
End If
If ComboBox2.Value = "" Then
MsgBox "Please Select the Database Name!!"
GoTo Last
End If
If ComboBox1.Value = "" And ComboBox2.Value = "" Then
MsgBox "Please Select Server and Database Name!!"
GoTo Last
End If
If CheckBox1.Value = True Then 'Back Up Only
BkupDbname = ComboBox2.Value
Filename = "'E\Databases\" & Application.UserName & "\" & BkupDbname & "_" & VBA.Format(Now(), "YYYY_MM_DD_HH_MM_SS") & ".bak'"
Qry = "BACKUP DATABASE " & BkupDbname & " TO DISK = " & Filename
MsgBox Qry
Set Sheet6.rs = Sheet2.cnn1.Execute(Qry) 'ERROR COMES HERE
GoTo Last
Erred:
MsgBox "Error occurred!! BackUp Unsuccessfull."
End If
Last:
Application.ScreenUpdating = True
Application.Calculation = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Please help!!
Thanks & Regards,
Mohan Raj Krishnan
I am new to this forum. Need a little bit of help.
I am trying to run a piece of code when i run the SQL Script it run well. But when i run the same script via VBA it throws the following error.
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147217871 (80040e31)':
Automation error
---------------------------
OK Help
---------------------------
Private Sub CommandButton4_Click()
On Error GoTo Erred
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.DisplayPageBreaks = False
Dim Qry As String
Dim i As Integer
Dim Filename As String
Dim BkupDbname As String
Sheet6.Db_name = "master"
If Sheet2.cnn1.State <> adStateOpen Then
Sheet2.connect2
End If
Set rs = Nothing
If Sheet2.checkerr2 = 1 Then
GoTo Last:
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox2.Value = False Then
MsgBox "Please Atleast One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox2.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox2.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True Then
MsgBox "Please Select Only One Checkbox!!"
GoTo Last
End If
If ComboBox1.Value = "" Then
MsgBox "Please Select the Server Name!!"
GoTo Last
End If
If ComboBox2.Value = "" Then
MsgBox "Please Select the Database Name!!"
GoTo Last
End If
If ComboBox1.Value = "" And ComboBox2.Value = "" Then
MsgBox "Please Select Server and Database Name!!"
GoTo Last
End If
If CheckBox1.Value = True Then 'Back Up Only
BkupDbname = ComboBox2.Value
Filename = "'E\Databases\" & Application.UserName & "\" & BkupDbname & "_" & VBA.Format(Now(), "YYYY_MM_DD_HH_MM_SS") & ".bak'"
Qry = "BACKUP DATABASE " & BkupDbname & " TO DISK = " & Filename
MsgBox Qry
Set Sheet6.rs = Sheet2.cnn1.Execute(Qry) 'ERROR COMES HERE
GoTo Last
Erred:
MsgBox "Error occurred!! BackUp Unsuccessfull."
End If
Last:
Application.ScreenUpdating = True
Application.Calculation = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Please help!!
Thanks & Regards,
Mohan Raj Krishnan