以Jet为单位备份SQL-Server数据 [英] Backing Up Ones SQL-Server Data as Jet

查看：68 发布时间：2019/6/6 20:22:14 access

本文介绍了以Jet为单位备份SQL-Server数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在远程sql服务器上有一个小数据库。服务器的所有者每天备份

db，但这些备份不是免费提供给我的。我希望我的数据

（只有我的数据）以某种形式备份，我可以访问。

我有一个adp。它只存在于连接到远程数据库。

我有一个mdb。它有一个表格。表单将在db的开头打开。这是表格背后的

代码。它会删除mdb中的所有表。它检查了adp中的表格

。它导入它们。它将它们复制到备份设备。它关闭了

db和访问应用程序。

我没有尝试使用任何中型或大型数据库，只有一个小的数据库。

我预计使用更大的数据库会花费太长时间;对于几张桌子和一些

百条记录大约需要11秒。

我每周日04:00通过Windows安排打开数据库，操作系统即使它花了一个小时
我也不会感到不便。几个星期以来，它已经无故障地工作了几个星期。我可以修改它以备份SPROC，VIEW和

功能，但我不需要它。

所以，它给了我在JET中的数据，没有介入。

（哎呀，我希望我之前没有发布过，但我认为不是。）

选项比较数据库

选项明确

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

设置c =新ADODB.Connection

随c

。打开CurrentProject.BaseConnectionString

结束

''获取表名

设r = c.OpenSchema（_

adSchemaTables，Array（空，空，空，表））

随r

Do While Not .EOF

CurrentProject.Connection.Execute（" DROP表"&！TABLE_NAME）

.MoveNext

循环

结束

' '刷新TableDefs

DBEngine（0）（0）.TableDefs.Refresh

''设置持久安全信息

''在adp文件中为true

Secur ityInformationTRUE

''连接到ADP文件

随c

。关闭

。打开b& " ;; USER ID =" &安培;你和你英寸; PASSWORD =" &安培; p $ />
结束

''获取表名

设r = c.OpenSchema（_

adSchemaTables，Array（Empty，Empty，Empty，Table））

''导入SQL表（作为JET）

用r

请勿.EOF

如果离开（！TABLE_NAME，2）<> "峰; dt"然后_

DoCmd.TransferDatabase acImport，Microsoft Access，_

ADPFile，acTable，！TABLE_NAME，！TABLE_NAME，False

。 MoveNext

循环

。关闭

结束

''设置持久安全信息

'在adp文件中为false

SecurityInformationFALSE

''将表复制到BackUp Device

SaveAsText 6，"，备份文件

DoCmd.Close acForm，Me.Name

End Sub

私人子表格_关闭（）

Application.Quit

结束子

私人Sub Form_Open（取消为整数）

BackupSQLTablesAsJET

End Sub

Private Sub SecurityInformation（ByVal vPERSIST As String）

Dim a As Access.Application

设置a =新的Access.Application

使用

.OpenAccessProject ADPFile
使用.CurrentP roject

如果.IsConnected则.CloseConnection

.OpenConnection替换（b，FALSE，vPERSIST），u，p

结束

.Quit

结束

结束子

-

Lyle

（电子邮件参考 http：// ffdba.com/contacts.htm ）

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)

以Jet为单位备份SQL-Server数据 [英] Backing Up Ones SQL-Server Data as Jet

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

以Jet为单位备份SQL-Server数据 [英] Backing Up Ones SQL-Server Data as Jet

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭