把获取的数据集导出到mdb中去,看看是什么错误,请指点一下,谢谢Private Sub Export2Mdb(pRSet As ADODB.Recordset, sFileName As String)
On Error GoTo ErrorHandler pRSet.MoveFirst
Dim pWrk As DAO.Workspace
Set pWrk = DAO.DBEngine.Workspaces(0)
Dim pDb As DAO.Database
Set pDb = pWrk.CreateDatabase(sFileName, dbLangGeneral, dbEncrypt)
Dim pTabDef As DAO.TableDef
Set pTabDef = pDb.CreateTableDef("ExportMDB")
Dim pField As DAO.Field
Dim FieldType As String
Dim I As Long
For Each pField In pRSet.Fields
Select Case pRSet.Fields(I).Type
Case adNumeric, adInteger, adDecimal, adDouble, adSingle
FieldType = "alter table exportmdb add column pField.Name NUMBER"
Execute FieldType
Case adVarChar, adChar
FieldType = "alter table exportmdb add column pField.Name TEXT(" & pField.DefinedSize & ")"
Execute FieldType
Case adBSTR, adWChar, adVarWChar, adLongVarChar, adLongVarWChar
FieldType = "alter table exportmdb add column pfield.Name TEXT(255)"
Execute strAlter
Case adDate, adDBDate, adDBTime, adDBTimeStamp
FieldType = "alter table exportmdb add column pfield.Name DATETIME"
Execute strAlter
Case Else
FieldType = "alter table exportmdb add column pfield.Name TEXT(255)"
Execute strAlter
End Select
Next pField
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = OpenDatabase(strFileName)
Set rs = db.OpenRecordset("SELECT * FROM exportmdb")
While Not pRSet.EOF
rs.AddNew
For I = 0 To pRSet.Fields.Count - 1
rs.Fields(I) = pRSet.Fields(I)
Next
rs.Update
pRSet.MoveNext
Loop MsgBox "导出完毕!"
Exit Sub
ErrorHandler:
HandleError False, "Export2Mdb " & c_sModuleFileName & " " & GetErrorLineNumberString(Erl), Err.Number, Err.Source, Err.Description, 4
End Sub
On Error GoTo ErrorHandler pRSet.MoveFirst
Dim pWrk As DAO.Workspace
Set pWrk = DAO.DBEngine.Workspaces(0)
Dim pDb As DAO.Database
Set pDb = pWrk.CreateDatabase(sFileName, dbLangGeneral, dbEncrypt)
Dim pTabDef As DAO.TableDef
Set pTabDef = pDb.CreateTableDef("ExportMDB")
Dim pField As DAO.Field
Dim FieldType As String
Dim I As Long
For Each pField In pRSet.Fields
Select Case pRSet.Fields(I).Type
Case adNumeric, adInteger, adDecimal, adDouble, adSingle
FieldType = "alter table exportmdb add column pField.Name NUMBER"
Execute FieldType
Case adVarChar, adChar
FieldType = "alter table exportmdb add column pField.Name TEXT(" & pField.DefinedSize & ")"
Execute FieldType
Case adBSTR, adWChar, adVarWChar, adLongVarChar, adLongVarWChar
FieldType = "alter table exportmdb add column pfield.Name TEXT(255)"
Execute strAlter
Case adDate, adDBDate, adDBTime, adDBTimeStamp
FieldType = "alter table exportmdb add column pfield.Name DATETIME"
Execute strAlter
Case Else
FieldType = "alter table exportmdb add column pfield.Name TEXT(255)"
Execute strAlter
End Select
Next pField
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = OpenDatabase(strFileName)
Set rs = db.OpenRecordset("SELECT * FROM exportmdb")
While Not pRSet.EOF
rs.AddNew
For I = 0 To pRSet.Fields.Count - 1
rs.Fields(I) = pRSet.Fields(I)
Next
rs.Update
pRSet.MoveNext
Loop MsgBox "导出完毕!"
Exit Sub
ErrorHandler:
HandleError False, "Export2Mdb " & c_sModuleFileName & " " & GetErrorLineNumberString(Erl), Err.Number, Err.Source, Err.Description, 4
End Sub
建议使用select ... into 'd:\a.mdb' 的方式。
While ...
......
Loop应该:
While ...
......
Wend
DO WHILE Not pRSet.EOF
LOOP
也可以
先将
On Error GoTo ErrorHandler 注释掉然后在程序里执行,看在什么地方出错
FieldType = "alter table exportmdb add column pfield.Name DATETIME"
Execute strAlter如何下面出现
Execute strAlter?
2.For I = 0 To pRSet.Fields.Count - 1
rs.Fields(I) = pRSet.Fields(I)
Next
rs.Update
rs.Update 应该置于 FOR NEXT循环内
1 将所有的 strFieldType 改为 strAlter
2 所有对 pField 的引用都放到括号外,如:
strAlter = "alter table exportmdb add column " & pField.Name & " NUMBER"3 所有的 Execute 改为 pDb.Execute4 删除下列代码:
Dim db As DAO.Database
Set db = OpenDatabase(strFileName)
并改下一句为:
Set rs = pDb.OpenRecordset("SELECT * FROM exportmdb")
2.For I = 0 To pRSet.Fields.Count - 1
rs.Fields(I) = pRSet.Fields(I)
Next
rs.Update
rs.Update 应该置于 FOR NEXT循环内
--------------------------------------原来是对的。再仔细看看。