automate table export (1 Viewer)

[kobiashi]

hi


i have a sub that exports all tables into an excel spreadsheet, is there any way i can automate this for a specific time of day?

here is my code

Private Sub btnExportDatabase_Click()
    Dim td As DAO.TableDef, db As DAO.Database
    Dim out_file As String
    
    out_file = "S:\Systems Eng\Rolling Stock\MCU\BACKEND DATABASE DO NOT TOUCH" & "\KADBEBackUp" & Format(Date, "ddmmyyy") & ".xls"
    
    Set db = CurrentDb()
       For Each td In db.TableDefs
         If Left(td.Name, 4) = "MSys" Then
         '// do nothing -- skip
       Else
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         td.Name, out_file, True, Replace(td.Name, "dbo_", "")
       End If
       Next
End Sub

 

[Minty]

The answer to this depends on if the database is guaranteed to be open when you want it to run.
If it is, then you could easily use a hidden form timer event to check the the current time against a time window you want the process to run in. Alternatively you could store the last run time in a table and only run the task if that is >24hrs ago for a daily back up.

If it isn't then you could set up a windows scheduled task to open the database using a command line switch to force the function to run via a macro.

Ideally however if the data is really important you should probably look at moving your BE database to a more suitable storage method - SQL Server express is free and has much more robust backup facilities inbuilt.

 

[kobiashi]

thanks for the advice minty

i think i should go for the keep access open option, as some one will always be using it so it'll be open somewhere.

i cant use sql express, which is a shame, but the organisation i work for, the IT department as very reluctant to install software such as that, for example, they have purchased office 365, but refuse to upgrade from 2013.

 

[isladogs]

If it's multi user and someone will always be logged in, isn't there a risk the task will be duplicated?
In the same situation as you, I created a separate utility to run as a scheduled task on one pc and then close automatically.