Automation Error Run-time error '-2147217871 (80040e31)'

[krishnamo]

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]

what is .rs and .cnn1?

use ADODB.Connection object to do this

[krishnamo]

rs is the ADODB Recordset and cnn1 is the ADODB.connection.

[mohanvijay]

can you post your workbook here?

[krishnamo]

I can't paste the worksheet as it contains some confidential information. Please any other ways of help?

[Aussiebear]

Post a sample workbook with the same layout

[krishnamo]

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]

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