在几台机上运行时都可以将TXT文件导入数据库中的临时表TempTable,且无问题。可有一台机子在将TXT文件导入数据库中的临时表TempTable时,只产生一列。比如一条记录“2006-06-21| 11:08:29| 1|C|28|M”,正常来说是在T_data字段下存2006-06-21,在T_time字段下存11:08:29,在T_address字段下存1,在T_connmode字段下存C,在T_command字段下存28,在T_automatic字段下存M。但是在那台机上“2006-06-21| 11:08:29| 1|C|28|M”存在一个叫F1的字段下。我都不知道该怎么解决了。
我的TXT文件名为R.txt,内容如下: 2006-06-21| 11:08:29| 1|C|28|M
2006-06-21| 11:08:34| 2|R|30|M
2006-06-21| 11:08:36| 3|R|30|M
2006-06-21| 11:08:36| 1|C|14|M
2006-06-21| 11:08:41| 2|R|26|M
2006-06-21| 11:08:43| 3|R|26|M
2006-06-21| 11:27:23| 1|C|21|M我用“|”做为分割符。程序里是这样写的:
'产生Schemia.ini文件
Call WriteTempSchemia("R.txt", "|") '以"|"作为分隔符'将TXT文件导入数据库中的临时表
Call TxtToMdb(App.Path & "\R.txt", App.Path & "\Alarm.mdb", "TempTable")Public Sub WriteTempSchemia(strFileName As String, strSeparator As String)
'写入格式符号文件,在当前目录下产生Schema文件
Open App.Path & "\Schema.ini" For Output As #1
Print #1, "[" & strFileName & "]"
Print #1, "Format=Delimited(" & strSeparator & ")"
Print #1, "ColNameHeader=true"
Print #1, "MaxScanRows = 0"
Print #1, "Col1=T_data Text Width 20"
Print #1, "Col2=T_time Text Width 20"
Print #1, "Col3=T_address Text Width 10"
Print #1, "Col4=T_connmode Text Width 10"
Print #1, "Col5=T_command Text Width 20"
Print #1, "Col6=T_automatic Text Width 6"
Close #1
End SubPrivate Sub TxtToMdb(sTxtPath As String, sTxtFileName As String, sAccessFullFileName As String, sAccessTable As String)
'功能:将文本文件导入到Access中的表 On Error Resume Next
If cnn.State = 0 Then cnn.Open strCnn
cnn.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName If Err.Number = -2147217900 Then
'表已存在,则先删除再导
cnn.Execute "drop TABLE TempTable"
cnn.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName
End If
'导入后要将空行都去掉
cnn.Execute "delete * from " & sAccessTable & " where T_data is null"
cnn.Close
End Sub
我的TXT文件名为R.txt,内容如下: 2006-06-21| 11:08:29| 1|C|28|M
2006-06-21| 11:08:34| 2|R|30|M
2006-06-21| 11:08:36| 3|R|30|M
2006-06-21| 11:08:36| 1|C|14|M
2006-06-21| 11:08:41| 2|R|26|M
2006-06-21| 11:08:43| 3|R|26|M
2006-06-21| 11:27:23| 1|C|21|M我用“|”做为分割符。程序里是这样写的:
'产生Schemia.ini文件
Call WriteTempSchemia("R.txt", "|") '以"|"作为分隔符'将TXT文件导入数据库中的临时表
Call TxtToMdb(App.Path & "\R.txt", App.Path & "\Alarm.mdb", "TempTable")Public Sub WriteTempSchemia(strFileName As String, strSeparator As String)
'写入格式符号文件,在当前目录下产生Schema文件
Open App.Path & "\Schema.ini" For Output As #1
Print #1, "[" & strFileName & "]"
Print #1, "Format=Delimited(" & strSeparator & ")"
Print #1, "ColNameHeader=true"
Print #1, "MaxScanRows = 0"
Print #1, "Col1=T_data Text Width 20"
Print #1, "Col2=T_time Text Width 20"
Print #1, "Col3=T_address Text Width 10"
Print #1, "Col4=T_connmode Text Width 10"
Print #1, "Col5=T_command Text Width 20"
Print #1, "Col6=T_automatic Text Width 6"
Close #1
End SubPrivate Sub TxtToMdb(sTxtPath As String, sTxtFileName As String, sAccessFullFileName As String, sAccessTable As String)
'功能:将文本文件导入到Access中的表 On Error Resume Next
If cnn.State = 0 Then cnn.Open strCnn
cnn.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName If Err.Number = -2147217900 Then
'表已存在,则先删除再导
cnn.Execute "drop TABLE TempTable"
cnn.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName
End If
'导入后要将空行都去掉
cnn.Execute "delete * from " & sAccessTable & " where T_data is null"
cnn.Close
End Sub
Private Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)Private Sub TxtToMdb(sTxtPath As String, sTxtFileName As String, sAccessFullFileName As String, sAccessTable As String)
'功能:将文本文件导入到Access中的表start:
If Dir(App.Path & "\Schema.ini") = "" Then
Sleep 20
If Dir(App.Path & "\Schema.ini") = "" Then
If MsgBox("Schema.ini 未找到!", VbRetryCancel) = vbRetry Then
Goto start
Else
Exit Sub
End If
End If
End If'......原因是写 Schema 文件时需要磁盘交换,如果导入操作抢在它前面,则会出现你所说的情况。另,如果受权限制约而不能创建 Schema 文件,而你又屏蔽了错误提示,也会出现类似情况。