access转成.TXT,可以使用程序控制,灵活性大 text中的列宽度是根据字段的大小,你可以自行设定Option Explicit Dim conn As ADODB.Connection Dim rs As ADODB.RecordsetPrivate Sub cmdExport_Click() Dim fnum As Integer Dim file_name As String Dim num_fields As Integer Dim field_width() As Integer Dim field_value As String Dim i As Integer Dim num_processed As IntegerOn Error GoTo MiscError'打开输出文件 fnum = FreeFile file_name = App.Path & "\books.txt" Open file_name For Output As fnum Set rs = New ADODB.Recordset rs.Open "SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate FROM employees ", conn, adOpenDynamic, adLockPessimistic num_fields = rs.Fields.Count ReDim field_width(0 To num_fields - 1) For i = 0 To num_fields - 1 field_width(i) = rs.Fields(i).DefinedSize If field_width(i) < Len(rs.Fields(i).Name) Then field_width(i) = Len(rs.Fields(i).Name) End If field_width(i) = field_width(i) + 1 Print #fnum, rs.Fields(i).Name; Print #fnum, Space(field_width(i) - Len(rs.Fields(i).Name)) Next i Print #fnum, "" Do While Not rs.EOF num_processed = num_processed + 1 For i = 0 To num_fields - 1 field_value = rs.Fields(i).Value & "" Print #fnum, field_value & Space(field_width(i) - Len(field_value)) Next i Print #fnum, "" rs.MoveNext Loop rs.Close Close fnum MsgBox "成功导出了 " & Format(num_processed) & " 条记录." conn.Close Exit SubMiscError: MsgBox "Error " & Err.Number & vbCrLf & Err.Description End Sub Private Sub Form_Load() Set conn = New ADODB.Connection conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=yang"
DisplayRecordset End Sub Private Sub DisplayRecordset() ' 列出 Recordset 的所有记录 Dim S As String, i As Integer
rs.MoveFirst List1.Clear While Not rs.EOF S = "" For i = 0 To rs.Fields.Count - 1 S = S & rs.Fields(i).Value & vbTab Next List1.AddItem S rs.MoveNext Wend End Sub
以下是我的程序中用到的一种方式,没时间整理出导入的那部分,大家要用的自己看看吧.'================================================================= Dim WithEvents ADODB As Recordset Dim fsoFile As New FileSystemObject Dim ts As TextStream Dim JRO As New JetEngine Dim f_Path As String Private Sub CompDb() Set JRO = New JRO.JetEngine JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\tmp.mdb;Jet OLEDB:Engine Type=5" '目的文件 FileCopy App.Path & "\DB\tmp.mdb", App.Path & "\DB\data.mdb" 'API函数,复制文件替换旧文 Kill App.Path & "\DB\tmp.mdb" '------------------------------------字典-------------- ' JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\Dict.mdb", _ ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\tmp.mdb;Jet OLEDB:Engine Type=5" '目的文件 ' FileCopy App.Path & "\DB\tmp.mdb", App.Path & "\DB\dict.mdb" 'API函数,复制文件替换旧文 ' Kill App.Path & "\DB\tmp.mdb"End Sub Private Sub Command1_Click() On Error GoTo InputError If dbType = 1 Then DB.Close Set DB = Nothing End If '================================================ '== 在此加入是否清空原库记录判断 '================================================ If Chk.value = 1 Then '============================================== '== 如果选择导入数据前要清库的话先清库再 '== 压缩一下数据库,主要是使数据库ID '============================================== Set DB = New Connection DB.CursorLocation = adUseClient DB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb;"
Dim del_Main As String, del_Bd As String del_Main = "delete * from main " DB.Execute del_Main
del_Bd = "delete * from bd " DB.Execute del_Bd
' db.Close Set DB = Nothing
CompDb End If
If dbType = 1 Then Set DB = New Connection DB.CursorLocation = adUseClient DB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb;" End If Dim b() As String
Set ADODB = New Recordset With ADODB .LockType = 3 .CursorType = 1 .Open "SELECT * FROM main", DB '指示编辑过程中对记录使用的锁定类型
Set ts = fsoFile.OpenTextFile(f_Path, ForReading) ' ts.SkipLine '跳过前面两行 ' ts.SkipLine Dim strLine As String
' db.Close MsgBox "导入完成,请重新启动系统!", 48, "提示" Shutdown InputError: If Err.Number <> 0 Then MsgBox Err.Description End If
End Sub Private Sub Command2_Click() On Error Resume Next
With cdlog1 .DialogTitle = "数据导入" .InitDir = App.Path .Filter = "(数据库)*.txt|*.txt" .CancelError = True .Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist .ShowOpen f_Path = .FileName End With
If Err.Number <> cdlCancel Then frm.Caption = f_Path End If End Sub Private Sub Command3_Click() Set ADODB = Nothing Unload Me End SubPrivate Sub Form_Load() If dbType = 1 Then Chk.Enabled = True Else Chk.Enabled = False End If End Sub '===============================================================================
text中的列宽度是根据字段的大小,你可以自行设定Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.RecordsetPrivate Sub cmdExport_Click()
Dim fnum As Integer
Dim file_name As String
Dim num_fields As Integer
Dim field_width() As Integer
Dim field_value As String
Dim i As Integer
Dim num_processed As IntegerOn Error GoTo MiscError'打开输出文件
fnum = FreeFile
file_name = App.Path & "\books.txt"
Open file_name For Output As fnum Set rs = New ADODB.Recordset
rs.Open "SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate FROM employees ", conn, adOpenDynamic, adLockPessimistic num_fields = rs.Fields.Count
ReDim field_width(0 To num_fields - 1)
For i = 0 To num_fields - 1
field_width(i) = rs.Fields(i).DefinedSize
If field_width(i) < Len(rs.Fields(i).Name) Then
field_width(i) = Len(rs.Fields(i).Name)
End If
field_width(i) = field_width(i) + 1
Print #fnum, rs.Fields(i).Name;
Print #fnum, Space(field_width(i) - Len(rs.Fields(i).Name))
Next i
Print #fnum, "" Do While Not rs.EOF
num_processed = num_processed + 1
For i = 0 To num_fields - 1
field_value = rs.Fields(i).Value & ""
Print #fnum, field_value & Space(field_width(i) - Len(field_value))
Next i
Print #fnum, ""
rs.MoveNext
Loop rs.Close
Close fnum
MsgBox "成功导出了 " & Format(num_processed) & " 条记录."
conn.Close
Exit SubMiscError:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
End Sub
Private Sub Form_Load()
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=yang"
End Sub
http://community.csdn.net/Expert/topic/2659/2659996.xml?temp=8.181399E-02
.....
Loop
这个可改用GetString一次性得到。
-----------------------------------------GetString 方法 (ADO Recordset)
将 Recordset 作为字符串返回。语法Set Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)返回值将 Recordset 按字符串值的变体型 (BSTR) 返回。参数StringFormat 指定 Recordset 应转换为下列格式。常量 说明
adClipString 行由 RowDelimiter 分界,列由 ColumnDelimiter 分界,NULL 值由 NullExpr 分界。这三个参数只有在与 adClipString 一起时才有效。
NumRows 可选。记录集要转换的行数。如果没有指定 NumRows,或者它大于记录集的总行数,则记录集的所有行都要转换。ColumnDelimiter 可选。如果指定则为列与列之间的分界符,否则为 TAB 字符。RowDelimiter 可选。如果指定则为行与行之间的分界符,否则为 CARRIAGE RETURN 字符。NullExpr 可选。如果指定则为 NULL 值处的表达式,否则为空字符串。 说明行数据(但不是模式数据)保存在串中。因此不能使用该字符串重新打开记录集。
打开TXT文件,遍历取值就行了。
Dim ConnStr As String
ConnStr = "Provider=MSDASQL.1;" & _
"DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=E:\DEMO\txt"
conn.Open ConnStr
rs.CursorLocation = adUseClient
rs.Open "Select * From score2.txt", conn, adOpenDynamic, adLockPessimistic
DisplayRecordset
End Sub
Private Sub DisplayRecordset() ' 列出 Recordset 的所有记录
Dim S As String, i As Integer
rs.MoveFirst
List1.Clear
While Not rs.EOF
S = ""
For i = 0 To rs.Fields.Count - 1
S = S & rs.Fields(i).Value & vbTab
Next
List1.AddItem S
rs.MoveNext
Wend
End Sub
txt格式为:
编号 名称 账号 金额 代销
44010002 广州0002 5810221037524214 2012.36 12074.16
44010003 广州0003 5810221037524222 732.62 4395.72
44010004 广州0004 5810221037524230 993.96 5963.76
要求,读入前先判断所要读入的文件第一行是否相等(就是说字段要为
编号 名称 账号 金额 代销,空白处是一个
tab,另外读入的数放入ACCESS2000里后,前三个字段编号名称账号要为文本类一,而后面的金额和代销要为货币型,保留二个小数点.)
Dim WithEvents ADODB As Recordset
Dim fsoFile As New FileSystemObject
Dim ts As TextStream
Dim JRO As New JetEngine
Dim f_Path As String
Private Sub CompDb()
Set JRO = New JRO.JetEngine
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\tmp.mdb;Jet OLEDB:Engine Type=5" '目的文件 FileCopy App.Path & "\DB\tmp.mdb", App.Path & "\DB\data.mdb" 'API函数,复制文件替换旧文
Kill App.Path & "\DB\tmp.mdb" '------------------------------------字典--------------
' JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\Dict.mdb", _
' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\tmp.mdb;Jet OLEDB:Engine Type=5" '目的文件
' FileCopy App.Path & "\DB\tmp.mdb", App.Path & "\DB\dict.mdb" 'API函数,复制文件替换旧文
' Kill App.Path & "\DB\tmp.mdb"End Sub
Private Sub Command1_Click()
On Error GoTo InputError
If dbType = 1 Then
DB.Close
Set DB = Nothing
End If
'================================================
'== 在此加入是否清空原库记录判断
'================================================
If Chk.value = 1 Then
'==============================================
'== 如果选择导入数据前要清库的话先清库再
'== 压缩一下数据库,主要是使数据库ID
'==============================================
Set DB = New Connection
DB.CursorLocation = adUseClient
DB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb;"
Dim del_Main As String, del_Bd As String
del_Main = "delete * from main "
DB.Execute del_Main
del_Bd = "delete * from bd "
DB.Execute del_Bd
' db.Close
Set DB = Nothing
CompDb End If
If dbType = 1 Then
Set DB = New Connection
DB.CursorLocation = adUseClient
DB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB\DATA.mdb;"
End If
Dim b() As String
Set ADODB = New Recordset
With ADODB
.LockType = 3
.CursorType = 1
.Open "SELECT * FROM main", DB
'指示编辑过程中对记录使用的锁定类型
Set ts = fsoFile.OpenTextFile(f_Path, ForReading)
' ts.SkipLine '跳过前面两行
' ts.SkipLine
Dim strLine As String
DoEvents
While Not ts.AtEndOfLine '判断是否到文件最后一行
strLine = ts.ReadLine '读入一行(从一个 TextStream 文件读取一整行(到换行符但不包括换行符)并返回得到的字符串)
b = Split(strLine, ",") '每行分成 N 个字符串数组
.AddNew '导入数据库
.Fields("户号") = b(0)
.Fields("户别") = b(1)
.Fields("家庭关系") = b(2)
.Fields("姓名") = b(3)
.Fields("曾用名") = b(4)
.Fields("性别") = b(5)
.Fields("民族") = b(6)
.Fields("出生日期") = b(7)
.Fields("出生签发") = b(8)
.Fields("住址派出所") = b(9)
.Fields("住址居委会") = b(10)
.Fields("住址街路巷") = b(11)
.Fields("住址详址") = b(12)
.Fields("籍贯") = b(13)
.Fields("宗教信仰") = b(14)
.Fields("身份证号") = b(15)
.Fields("文化程度") = b(16)
.Fields("婚姻状况") = b(17)
.Fields("兵役状况") = b(18)
.Fields("变动日期") = b(19)
.Fields("变动原因") = b(20)
.Update '更新
Wend
ts.Close
.Close
End With
' db.Close
MsgBox "导入完成,请重新启动系统!", 48, "提示"
Shutdown
InputError:
If Err.Number <> 0 Then
MsgBox Err.Description
End If
End Sub
Private Sub Command2_Click()
On Error Resume Next
With cdlog1
.DialogTitle = "数据导入"
.InitDir = App.Path
.Filter = "(数据库)*.txt|*.txt"
.CancelError = True
.Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist
.ShowOpen
f_Path = .FileName
End With
If Err.Number <> cdlCancel Then
frm.Caption = f_Path
End If
End Sub
Private Sub Command3_Click()
Set ADODB = Nothing
Unload Me
End SubPrivate Sub Form_Load()
If dbType = 1 Then
Chk.Enabled = True
Else
Chk.Enabled = False
End If
End Sub
'===============================================================================