Execute SQL Database Backup from Access Front-End

Question

I have created a script to back up a specific database in SQL. I have tested it and it works fine when executed in SQL.

I want to be able to execute this script using Access Front-End to enable an end user to run a back-up when they require.

The query seems to be timing out and I've tried multiple different methods in executing the query.

This is my script to execute a backup.

CREATE PROCEDURE [dbo].[ASP_XXBackUp]
(@name VARCHAR(50), -- database name  
 @RETURNVALUE CHAR(1) = NULL OUTPUT)
AS
BEGIN 

DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'E:\SQL-Backup\ManualBackup\'  

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) +
         REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108),':','')

-- specify filename
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

--Execute BackUp Sequence
BACKUP DATABASE @name TO  DISK = @fileName 

--Check that we managed to create a successful backup
SELECT TOP 1 @RETURNVALUE = dbo.fc_FileExists(@fileName);

-- Check if succeeded to find.
IF @@ROWCOUNT = 1
    SELECT @RETURNVALUE 
ELSE
    SELECT @RETURNVALUE = 0 --we have failed

END

Is there a way for me to be able to execute this in Access Front-End?

Methods I have tried in Access to execute:

METHOD 1 Dim blnReturnValue As Boolean Dim cmd As ADODB.Command

blnReturnValue = False 'Assume failure
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = gcnn
    .CommandText = "ASP_XXBackUp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@name", adVarChar, adParamInput, 50, gDatabase)
    .Parameters.Append .CreateParameter("@RETURNVALUE", adChar, adParamInputOutput, 1)
    .Execute

    blnReturnValue = .Parameters("@RETURNVALUE")
    Set cmd = Nothing
End With

If blnReturnValue = False Then
    Me.ChkBackUp = False
    MsgBox "ERROR!: Backup Failed!" & vbCrLf & "Please contact your administrator!"
Else
    Me.ChkBackUp = True
    MsgBox "SUCCESS!: Databased Backed Up!"
End If

METHOD 2

gcnn.Execute "EXEC ASP_XXBackUp '" & gDatabase & "'"

METHOD 3

'Run proc to create journals for Year end
strSQL = "EXEC ASP_XXBackUp '" & gDatabase & "'"

Call ChangeQueryDef("qryPassThrough_SQL2", strSQL)
Call SQLPassThrough(strQDFName:="qryPassThrough_SQL2", strSQL:=strSQL, strConnect:=gODBCConn)

blnReturnValue = DCount("*", "qryPassThrough_SQL2")

This is the Error Message I get:

Do you mean actually 'timing out'? What is the exact error you get? How long does this take when run in SSMS? What code are you using to run it from MS Access? — Nick.McDermaid
Updated the above @Nick.McDermaid When I run it in SQL it takes about 3-5 minutes to complete the backup. — Justin
Does the error always appear after 30 seconds? There is a default query timeout of 30 seconds - you need to extend this to 10 minutes. I believe this is the property you need to alter: msdn.microsoft.com/en-us/library/ms678265(v=vs.85).aspx — Nick.McDermaid
@Nick.McDermaid this is the solution I was looking for! Thanks — Justin

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2017-01-06T19:06:45

The most simple code and approach is to create a pass-through query in Access. That way you don’t mess with connection stings etc. in code.

The VBA code to do this would be:

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "MyCoolStoredProc"
  .Execute
End With

Note how VERY simple the above code is to execute that stored procedure.

Of course the time out you set in the property sheet for this pass-through query will be used. So you can edit the property sheet, or simple in code set the ODBC timeout.

The above “one” query qryPassR in above will OFTEN be used in a zillion places in your VBA code. So you can set the .SQL for anything as per above.

Keep in mind that you should set the "returns records" in the property sheet for the pass-through. However, since we have this "catch-all" query, then just set the returns records as required (so for stored procedures and T-SQL commands that don't return records, then set the value = false, and true for SQL SELECT or anything else that returns data).

So you for example might want to execute some code that does NOT return a value (records) from the stored procedure, so you code will more look like this:

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "MyCoolStoredProc"
  .ReturnsRecords = False
  .Execute
End With

However, in your case, you want records returned, and want to increase the ODBC timeout to say 5 minutes, so you can use this:

Dim retValue      As Integer

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "EXEC ASP_XXBackUp '" & gDatabase & "'"
  .ReturnsRecords = True
  .ODBCTimeout = 300   ' 300 seconds = 5 minutes
  retValue = .OpenRecordset()(0)
End With

Debug.Print "ret value = " & retValue

Note that in above, the “exec” is optional (just like it is when entering it in SQL Server Management Studio). so we could use:

  .SQL = "ASP_XXBackUp '" & gDatabase & "'"

So once you create ONE pass-through query, then you can use it every place that you need to execute T-SQL or any SQL as pass-through server side. Note in above we used OpenRecordSet so we MUST set ReturnsRecords = True. If you not returning records, then you want to use .Execute in place of .OpenRecordSet.

Execute SQL Database Backup from Access Front-End

1 Answers