试试,直接运行 SQL 语句: Select * Into tablename In 'C:\yourpath\backup.mdb' From SqlServerTable
我写了个过程你自己看吧 Public Function CrtRptMdb(MyResultset As rdoResultset, cfilename As String, cTableName As String, Optional bDropDB As Boolean) Dim cMySql As String Dim cNewTable As String Dim MYDB As Database Dim Myrecordset As Variant Dim I As Integer Dim nFieldNum As Integer Dim cField As String On Error Resume Next On Error GoTo errorhandle cfilename = UCase(Trim(cfilename)) If bDropDB = True And Dir(App.Path + "\" + cfilename + ".MDB") <> "" Then Kill App.Path + "\" + cfilename + ".MDB" If Dir(App.Path + "\" + cfilename + ".MDB") = "" Then Set MYDB = DBEngine.Workspaces(0).CreateDatabase(App.Path + "\" + cfilename + ".mdb", dbLangGeneral, dbEncrypt) Else Set MYDB = DBEngine.Workspaces(0).OpenDatabase(App.Path + "\" + cfilename + ".mdb") End If If Trim(cTableName) = "" Then MsgBox "You must offer table name when creating report table !!", vbCritical Exit Function End If On Error Resume Next MYDB.Execute "drop table " & cTableName On Error GoTo errorhandle cNewTable = "Create Table " & cTableName & " (" With MyResultset For I = 0 To .rdoColumns.Count - 1 Dim colname As String colname = .rdoColumns(I).name '*********************************************************************** '*** tBinary -2 tBit -7 tChar 1. tDateTime 11 tDecimal 3 tfloar 6 tImage -4 '*** tInt 4. tMoney 3. tNumeric 2. tReal 7 tSmalldatetime 11 tSmallint 5. '*** tSmallmoney 3 tSysname 12. tText -1 tTimestamp -2 tTinyint -6. '*** tVarbinary -3 tVarchar 12 '*********************************************************************** Select Case .rdoColumns(I).Type Case 1, 12 cField = colname & " Char(" & .rdoColumns(I).Size & ")" Case -6, 5 cField = colname & " Integer" Case 4 cField = colname & " Long" Case 2, 6, 7 If .rdoColumns(I).Size < 6 Then cField = colname & " Single" Else cField = colname & " Double" End If Case 3 cField = colname & " Currency" Case -7 cField = colname & " Boolean" Case 11, -2 cField = colname & " Date" Case -4, -1, -3 cField = colname & " Memo" Case Else cField = colname & " Char(" & .rdoColumns(I).Size & ")" End Select cNewTable = cNewTable & cField & "," Next End With cNewTable = Mid(cNewTable, 1, Len(cNewTable) - 1) & ")" MYDB.Execute cNewTable Set Myrecordset = MYDB.OpenRecordset("select * from " & cTableName, 2) nFieldNum = MyResultset.rdoColumns.Count - 1 If Not MyResultset.EOF Then MyResultset.MoveFirst End If Do While Not MyResultset.EOF Myrecordset.AddNew For I = 0 To nFieldNum Myrecordset.Fields(I).value = MyResultset.rdoColumns(I).value Next Myrecordset.Update MyResultset.MoveNext Loop Exit Function errorhandle: Set MYDB = Nothing MsgBox Err.Description, vbCritical End Function
然后 下一步...
为什么不用SQL备份 导入到Access中做什么啊?
好像Access比SQL还要不安全啊!
Select * Into tablename In 'C:\yourpath\backup.mdb' From SqlServerTable
Public Function CrtRptMdb(MyResultset As rdoResultset, cfilename As String, cTableName As String, Optional bDropDB As Boolean)
Dim cMySql As String
Dim cNewTable As String
Dim MYDB As Database
Dim Myrecordset As Variant
Dim I As Integer
Dim nFieldNum As Integer
Dim cField As String
On Error Resume Next
On Error GoTo errorhandle
cfilename = UCase(Trim(cfilename))
If bDropDB = True And Dir(App.Path + "\" + cfilename + ".MDB") <> "" Then Kill App.Path + "\" + cfilename + ".MDB"
If Dir(App.Path + "\" + cfilename + ".MDB") = "" Then
Set MYDB = DBEngine.Workspaces(0).CreateDatabase(App.Path + "\" + cfilename + ".mdb", dbLangGeneral, dbEncrypt)
Else
Set MYDB = DBEngine.Workspaces(0).OpenDatabase(App.Path + "\" + cfilename + ".mdb")
End If If Trim(cTableName) = "" Then
MsgBox "You must offer table name when creating report table !!", vbCritical
Exit Function
End If
On Error Resume Next
MYDB.Execute "drop table " & cTableName On Error GoTo errorhandle
cNewTable = "Create Table " & cTableName & " ("
With MyResultset
For I = 0 To .rdoColumns.Count - 1
Dim colname As String
colname = .rdoColumns(I).name '***********************************************************************
'*** tBinary -2 tBit -7 tChar 1. tDateTime 11 tDecimal 3 tfloar 6 tImage -4
'*** tInt 4. tMoney 3. tNumeric 2. tReal 7 tSmalldatetime 11 tSmallint 5.
'*** tSmallmoney 3 tSysname 12. tText -1 tTimestamp -2 tTinyint -6.
'*** tVarbinary -3 tVarchar 12
'***********************************************************************
Select Case .rdoColumns(I).Type
Case 1, 12
cField = colname & " Char(" & .rdoColumns(I).Size & ")"
Case -6, 5
cField = colname & " Integer"
Case 4
cField = colname & " Long"
Case 2, 6, 7
If .rdoColumns(I).Size < 6 Then
cField = colname & " Single"
Else
cField = colname & " Double"
End If
Case 3
cField = colname & " Currency"
Case -7
cField = colname & " Boolean"
Case 11, -2
cField = colname & " Date"
Case -4, -1, -3
cField = colname & " Memo"
Case Else
cField = colname & " Char(" & .rdoColumns(I).Size & ")"
End Select
cNewTable = cNewTable & cField & ","
Next
End With
cNewTable = Mid(cNewTable, 1, Len(cNewTable) - 1) & ")"
MYDB.Execute cNewTable
Set Myrecordset = MYDB.OpenRecordset("select * from " & cTableName, 2)
nFieldNum = MyResultset.rdoColumns.Count - 1
If Not MyResultset.EOF Then
MyResultset.MoveFirst
End If
Do While Not MyResultset.EOF
Myrecordset.AddNew
For I = 0 To nFieldNum
Myrecordset.Fields(I).value = MyResultset.rdoColumns(I).value
Next
Myrecordset.Update
MyResultset.MoveNext
Loop
Exit Function
errorhandle:
Set MYDB = Nothing
MsgBox Err.Description, vbCritical
End Function