3、程序原码
采用微软DAO (Data Access Object) 数据模型, 打开Access 本地数据库, 并连接一个外部ODBC数据表, 拷贝该表结构完成, 有以下过程块CopyStru :
Private Sub copyStru ()
Set dbsTemp = wrkjet. OpenDatabase (tagFilName)// 链接表的过程
For i = 0 To tabN - 1
Set tdfLinked = dbsTemp. CreateTableDef (“linkTab”)
tdfLinked. Connect = “OdbC; DATABASE = xgsbgsys ; UID =
sa ; PWD = ; DSN = xgsdb ;”
tdfLinked. SourceTableName = tabName (i)
dbsTemp. TableDefs. Append tdfLinked
Set temp Tab = dbsTemp. CreateTableDef ()
temp Tab. Name = tabName (i)
//创建新表的过程
For Each fld In tdfLinked. Fields
Set newFil = temp Tab. CreateField (fld. Name , fld. Type ,fld. Size)
newFil. OrdinalPosition = fld. OrdinalPosition
newFil. Required = fld. Required
temp Tab. Fields. Append newFil
Next
//创建索引
For Each idx In tdfLinked. Indexes
Set newIdx = temp Tab. CreateIndex ()
With newIdx
Name = tabName (i) & ” x”
Fields = idx. Fields
Unique = idx. Unique
Primary = idx. Primary
End With
temp Tab. Indexes. Append newIdx
Next
dbsTemp. TableDefs. Append temp Tab
Set temp Tab = Nothing
dbsTemp. TableDefs. Delete ”linkTab”
Next i
dbsTemp. Close
Set dbsTemp = Nothing
wrkjet. Close
Set wrkjet = Nothing
End Sub
End Sub |
追加数据, 采用微软ADO (ActiveX data object) 数据模型, 分别操纵SQL Server 和Access 数据对象, 追加记录数据来完成, 有以下过程块CopyDa2ta :
Private Sub copyData ()
Set sourceCn = New adodb. Connection
sourceCn. CursorLocation = adUseServer
strSql =“PROVIDER = MSDASQL ; dsn = xgsdb ; uid = sa ;
pwd = ;”
sourceCn. Open strSql
Set targetCn = New adodb. Connection
targetCn. CursorLocation = adUseClient
targetCn. Open ” PROVIDER = Microsoft. Jet. OL Edb. 3.51 ;
Data Source = ”& tagFilName &“;”
End If //追加新表
For i = 0 To tabN - 1
Set targetRst = New adodb. Recordset
strSql = ”select 3 from ”& tabName (i)
targetRst. Open strSql , targetCn , adOpenStatic , adLockPes2simistic , adCmdText
Set sourceSet = New adodb. Recordset
strSql = ”select 3 from ”& tabName (i) & strSQLApp
sourceSet. Open strSql , sourceCn
zdN = sourceSet. Fields. Count
If sourceSet. EOF Then Go To hh
sourceSet. MoveFirst
Do While Not sourceSet. EOF
targetRst. AddNew
For j = 0 To zdN - 1
If Trim (sourceSet. Fields (j) . Value) = ””Then
targetRst. Fields (j) . Value = Null
Else
targetRst. Fields (j) . Value = Trim (sourceSet. Fields(j) . Value)
End If //复制记录
Next
targetRst. Update
sourceSet. MoveNext
Loop
recN = targetRst. RecordCount
hh :sourceSet. Close
Set sourceSet = Nothing
targetRst. Close
Set targetRst = Nothing
Next
targetCn. Close
Set targetCn = Nothing
sourceCn. Close
Set sourceCn = Nothing
End Sub //删除链接 |
其中字符数组tabName (i) 中存放需备份的各数据表名, strSQLAPP 字符串中存放对数据表的限制条件where 子句内容。
4、数据恢复
如果要从Access中将数据恢复到SQL Server中，方法类似，仅仅是拷贝记录的方向和以上相反，即从Access表往链接过来的SQL表中拷贝。
2/2 首页 上一页 1 2 |
