PDA

View Full Version : Automation Error Run-time error '-2147217871 (80040e31)'



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

mohanvijay
01-20-2013, 11:02 PM
what is .rs and .cnn1?

use ADODB.Connection object to do this

krishnamo
01-20-2013, 11:40 PM
rs is the ADODB Recordset and cnn1 is the ADODB.connection.

mohanvijay
01-20-2013, 11:50 PM
can you post your workbook here?

krishnamo
01-21-2013, 10:20 AM
I can't paste the worksheet as it contains some confidential information. Please any other ways of help?

Aussiebear
01-21-2013, 02:54 PM
Post a sample workbook with the same layout

krishnamo
01-22-2013, 11:29 AM
HI,


I have attached the File. The main idea is to automate the process of Backup of sql databases using excel VBA. All Databases are minimum 15GB size. When i run a back on a empty database same code works perfectly fine. Time out issue. Please help?

Thanks & Regards,
Mohan Raj Krishnan

krishnamo
01-24-2013, 11:14 AM
Hi,


Still i am not able to find a solution for this fix. Any body had chance to look the file ?


Thanks & Regards,
Mohan Raj Krishnan