access中的行数据大于Excel表的最大行数.如何自动将其导入多个表

来源:精英网 点击数:25049 　 | 收藏 | 复制

时　间:2010-12-20 00:00:00
作　者:MichaelSunny ID：7031 城市：重庆
摘　要:为管理数据超大，自动化处理，提供一种思路。得各位朋友指点，今汇于此。若有更好的方法，请朋友赐教。欢迎大家加入ACCESS论坛。
正　文:

可能ACCESS数据量过大，导入EXCEL表，超过65536行或1048576行。在此以OFFICE2003为例。

假如有一个数据库DATABASE.MDB，其中有一张表DATE1，在VBA中建立数据通道，然后用命令”Select * FROM DATE1“返回全体记录，利用循环一条一条拷贝到EXCEL表中，每复制65536条记录则换一张表

运行前把此文件复制到MDB所在目录下，然后修改代码中的数据库名称和表格名称，具体位置在代码中有注释

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Private Sub CommandButton8_Click()
Unload Me
End Sub
Private Sub CommandButton9_Click()
Dim RecordCount, SheetNO, Line
Application.ScreenUpdating = False
RecordCount = 1
SheetNO = 1
Line = 1
While Not rs.EOF
      Application.StatusBar = RecordCount
      RecordCount = RecordCount + 1
      If Line <= 65536 Then
         For J = 0 To rs.Fields.Count - 1
            ActiveSheet.Cells(Line, J + 1) = rs.Fields(J)
         Next
         rs.MoveNext
         Line = Line + 1
      Else
         Sheets.Add AFTER:=Sheets(SheetNO)
         SheetNO = SheetNO + 1
         Sheets(SheetNO).Select
         Line = 1
      End If
Wend
Application.ScreenUpdating = True
MsgBox "导入结束"
End Sub
Private Sub UserForm_Activate()
Dim DataPath As String
'数据库（*.MDB）必须在当前目录下，且文件名为:DATABASE.MDB，待导入的表名必须是：DATA，否则请修改下面的连接字符串
DataPath = ActiveWorkbook.Path & "\"
Set conn = New ADODB.Connection
conn.C + DataPath + "database.mdb;Persist Security Info=False"
conn.ConnectionTimeout = 5
conn.Open
sql = "select * from data"  '表名为：DATA
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenDynamic
End Sub
Private Sub UserForm_Terminate()
rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing
End Sub