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\Some Books.adp"
Const Backupfile As String = "E:\SomeBooks.m db"
Const b As String = "PROVIDER=SQLOL EDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=DB_A0A0 A0;" _
& "DATA SOURCE=Some.Rem ote.SQL.Server"
Const p As String = "X9X9X9"
Const u As String = "UserName"
Private Sub BackupSQLTables AsJET()
Dim c As ADODB.Connectio n
Dim r As ADODB.Recordset
' zap old tables
Set c = New ADODB.Connectio n
With c
.Open CurrentProject. BaseConnectionS tring
End With
' get table names
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
With r
Do While Not .EOF
CurrentProject. Connection.Exec ute ("DROP TABLE " & !TABLE_NAME)
.MoveNext
Loop
End With
' refresh TableDefs
DBEngine(0)(0). TableDefs.Refre sh
' set persist security information
' in the adp file to true
SecurityInforma tion "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_NAM E, 2) <> "dt" Then _
DoCmd.TransferD atabase 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
SecurityInforma tion "FALSE"
' copy the tables to the BackUp Device
SaveAsText 6, "", Backupfile
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Close()
Application.Qui t
End Sub
Private Sub Form_Open(Cance l As Integer)
BackupSQLTables AsJET
End Sub
Private Sub SecurityInforma tion(ByVal vPERSIST As String)
Dim a As Access.Applicat ion
Set a = New Access.Applicat ion
With a
.OpenAccessProj ect ADPFile
With .CurrentProject
If .IsConnected Then .CloseConnectio n
.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)
