I have a tiny db on a remote sql server. The owner of the sever backs up the
db daily, but these backups are not freely available to me. I want my data
(only my data) backed up in some form that is accessible to me.
I have an adp. It exists only to connect to the remote db.
I have an mdb. It has a form. The form opens on the db’s opening. This is the
code behind the form. It zaps any tables in the mdb. It examines the tables
in the adp. It imports them. It copies them to a backup device. It closes the
db and the access application.
I have not tried this with any medium or large sized db, only with a tiny db.
I expect it would take too long with a larger db; for a few tables and a few
hundred records it takes about 11 seconds.
I open the db through Windows scheduling every Sunday at 04:00, os even if it
took an hour I would not be inconvenienced. It has worked without incident
for several weeks now. I could modify it to back up SPROCs, VIEWs and
FUNCTIONs but I don’t need that.
So, it gives me my data in JET, without intervention.
(Gee, I hope I haven't posted this before, but I think not.)
Option Compare Database
Option Explicit
Const ADPFile As String = "F:\Access\SomeBooks.adp"
Const Backupfile As String = "E:\SomeBooks.mdb"
Const b As String = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=DB_A0A0A0;" _
& "DATA SOURCE=Some.Remote.SQL.Server"
Const p As String = "X9X9X9"
Const u As String = "UserName"
Private Sub BackupSQLTablesAsJET()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
' zap old tables
Set c = New ADODB.Connection
With c
.Open CurrentProject.BaseConnectionString
End With
' get table names
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
With r
Do While Not .EOF
CurrentProject.Connection.Execute ("DROP TABLE " & !TABLE_NAME)
.MoveNext
Loop
End With
' refresh TableDefs
DBEngine(0)(0).TableDefs.Refresh
' set persist security information
' in the adp file to true
SecurityInformation "TRUE"
' connect to the ADP file
With c
.Close
.Open b & ";USER ID=" & u & ";PASSWORD=" & p
End With
' get table names
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
' import the SQL tables (as JET)
With r
Do While Not .EOF
If Left(!TABLE_NAME, 2) <> "dt" Then _
DoCmd.TransferDatabase acImport, "Microsoft Access", _
ADPFile, acTable, !TABLE_NAME, !TABLE_NAME, False
.MoveNext
Loop
.Close
End With
' set persist security information
' in the adp file to false
SecurityInformation "FALSE"
' copy the tables to the BackUp Device
SaveAsText 6, "", Backupfile
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Close()
Application.Quit
End Sub
Private Sub Form_Open(Cancel As Integer)
BackupSQLTablesAsJET
End Sub
Private Sub SecurityInformation(ByVal vPERSIST As String)
Dim a As Access.Application
Set a = New Access.Application
With a
.OpenAccessProject ADPFile
With .CurrentProject
If .IsConnected Then .CloseConnection
.OpenConnection Replace(b, "FALSE", vPERSIST), u, p
End With
.Quit
End With
End Sub
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
