Private Sub mdbTotxt(sAccessFullFileName As String, sTxtPath As String, sTxtFileName As String, sAccessTable As String)
'此过程将ACCESS文件转化为文本文件
'调用:Call mdbTotxt("c:\temp.mdb", "c:\","test.txt",table1)
'结果:将c:\temp.mdb中的table1表导出到c:\test.txt中
Dim db As DAO.Database
Set db = Workspaces(0).OpenDatabase(sAccessFullFileName)
db.Execute "SELECT * INTO [Text;DATABASE=" & sTxtPath & "].[" & sTxtFileName & "] FROM [" & sAccessTable & "]"
End Sub
Private Sub TxtToMdb(sTxtPath As String, sTxtFileName As String, sAccessFullFileName As String,sAccessTable As String)
'功能:将文本文件导入到Access中的表
'调用:Call TxtToMdb("c:\","test.txt","c:\a.mdb","NewTempTable")
'结果:将c:\test.txt导入到c:\a.mdb中的NewTempTable表中
Dim db As DAO.Database
Set db = DBEngine.CreateDatabase(sAccessFullFileName, dbLangGeneral)
If Err.Number = 3204 Then
Set db = Workspaces(0).OpenDatabase(sAccessFullFileName)
End If
db.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName
db.Close
Set db = Nothing
End Sub
或者:
Dim db As DAO.Database
Dim tbl As DAO.TableDef
On Error Resume Next
Set db = DBEngine.CreateDatabase("c:\a.mdb", dbLangGeneral)
If Err.Number = 3204 Then
Set db = Workspaces(0).OpenDatabase("c:\a.mdb")
End If
Set tbl = db.CreateTableDef("Temp")
tbl.Connect = "Text;database="c:\"
tbl.SourceTableName = "test#txt"
db.TableDefs.Append tbl
db.TableDefs.Delete "NewTempTable"
db.Execute "select temp.* into NewTempTable from temp"
db.TableDefs.Delete tbl.Name
db.Close
Set tbl = Nothing
Set db = Nothing
SELECT Table.Fields INTO [dbms type;DATABASE=path].[unqualified filename] FROM [Table or Tables]
SELECT Table.Fields INTO [MDB文件种类;DATABASE=MDB文件路径].[MDB文件文件名称] FROM [Table or Tables]
在 Form 上放置一个 CommandButton,加入 Microsoft DAO 3.51 Object Library,我们將使用 Biblio.mdb 的 authors Table,在 Command1_Click 中加入以下程序代码:
Dim db As Database
Set db = Workspaces(0).OpenDatabase(App.Path & "\biblio.mdb")
'db.execute "SELECT Table.Fields INTO [dbms type;DATABASE=path].[unqualified filename] FROM [Table or Tables]"
在以上程式中,db.execute 指令行之指令依MDB文件或文件的种类说明如下:
一、DBase文件
SQL 命令:SELECT * INTO [dBase III;DATABASE=MDB文件路径].[dbase文件名称] FROM [authors]
db.Execute "SELECT * INTO [dBase III;DATABASE=C:\test].[authors.DBF] FROM [authors]"
注意事項:
1、authors.DBF 事先不可存在,否则会产生错误!
2、若您沒有 Dbase,您可以使用 Access 来连結这个 Table,以便观察結果!
二、文本文件 (.Txt)
SQL 命令:SELECT * INTO [Text;DATABASE=文本文件路径].[文本文件名称] FROM [authors]
db.Execute "SELECT * INTO [Text;DATABASE=C:\test].[authors.TXT] FROM [authors]"
注意事項:
1、authors.TXT 事先不可存在,否则会产生错误!
2、此命令会产生的文件有二个,第一个就是文本文件 authors.TXT,第二个是 Schema.ini。
3、文本文件之格式为 CSV 之文件格式,以逗点分开,实际呈现方式如下:
"Au_ID","Author","Year Born"
1,"Jacobs, Russell",1950
2,"Metzger, Philip W.",1942
4、Schema.ini 若事先不存在会新产生一个,若已存在,则会在原文件后面直接 Append。
5、至于 Schema.ini 的內容为此次导出的相关资讯,格式同一般的 Ini 档,详細內容如下: