如何将数据从远程SQL Server复制到本地访问数据库 [英] How to copy data from Remote SQL Server to local access DB

问题描述

我正在尝试开发一种软​​件，该软件需要访问我们的中央SQL Server数据库，由于连接性较差，因此并非始终可用.因此，我想单击一下按钮，就将在线SQL Server的一些表(其中一些表有很多记录，并且大于40,000)复制到我的本地Access数据库中.

我在回答问题时遇到的问题是:什么是
的最有效方法? 将表或整个数据库复制到本地Access数据库中

没有人有任何示例代码可以向我展示如何有效地执行此操作.我不在乎覆盖本地数据.

I am trying to develop a software which required to access our central SQL server database which is not all the time available because of poor connectivity. so I want to copy some tables (some of them have lots of records > 40,000) form online SQL server to my local Access database on the click of a button.

The question I am having trouble answering is: What would be the most efficient way to
copy tables or entire database to the local Access database

Does anyone have any example code that would show me how to do this effectively. I don''t care about overwriting the local data. That would be okay in this case.

推荐答案

如果它是Microsoft SQL Server(写为我们的中央SQL Server")，则最好的方法是使用数据.

请参阅复制概述的类型 [

If it is Microsoft SQL Server ( as written "our central SQL server" ), the best way is to use replication of data.

See Types of Replication Overview[^] with MS SQL Server.

It is very reliable way to distribute SQL data over many locations.

Try
            
            Dim sDBFile As String = "SERVER PATH"
            Dim sBackUpFile As String = "D:\BACKUP"
            If Not System.IO.Directory.Exists(sBackUpFile) Then
                System.IO.Directory.CreateDirectory(sBackUpFile)
            End If
            '  a = Format

(Now.Date，"ddMMyyyyy") b = a c1 = Now.ToLongTimeString d = c1 c1 = c1.Replace(" ，" ") c1 = c1.Replace(" ，" ") d = c1 sBackUpFile& = " ' 首先检查您要压缩的文件是否存在 如果 File.Exists(sDBFile)然后 Dim db As 新建 OleDb.OleDbConnection ' CompactDatabase有两个参数，在目标路径上创建紧凑型数据库的副本 FileCopy(sDBFile，sBackUpFile) 结束 如果 ' 从压缩文件中还原原始文件 如果 File.Exists(sBackUpFile)然后 File.Delete(sBackUpFile) File.Copy(sDBFile，sBackUpFile，真) 结束 如果 捕获，例如 As 异常 MsgBox(例如消息) 结束 尝试 MsgBox(" & vbCrLf& 此备份可以在D:\ BACKUP \ ABC_today'sdate.mdb"，MsgBoxStyle.Information + MsgBoxStyle.OkOnly中找到， )

(Now.Date, "ddMMyyyy") b = a c1 = Now.ToLongTimeString d = c1 c1 = c1.Replace(":", "") c1 = c1.Replace(" ", "") d = c1 sBackUpFile &= "\ABC.mdb" 'First check the file u want to compact exists or not If File.Exists(sDBFile) Then Dim db As New OleDb.OleDbConnection 'CompactDatabase has two parameters, creates a copy of compact DB at the Destination path FileCopy(sDBFile, sBackUpFile) End If 'restore the original file from the compacted file If File.Exists(sBackUpFile) Then File.Delete(sBackUpFile) File.Copy(sDBFile, sBackUpFile, True) End If Catch ex As Exception MsgBox(ex.Message) End Try MsgBox("Backup of INTELPAT database was backedUp successfully !" & vbCrLf & "This Backup can bi found in D:\BACKUP\ABC_today'sdate.mdb ", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, )

这段代码将复制整个数据库....:)

This code will copy entire database....:)

这篇关于如何将数据从远程SQL Server复制到本地访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！