ado 中 insert 方法出错

cnwang

1   Dim dbf As Object
2   Set dbf = CreateObject("ADODB.Connection")
3   dbf.open "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDb=e:\IN_PORT"
4   dbf.execute "insert into b1 (code,name) values ('ccc','test')"
5   dbf.execute "insert into b1 (code,name) select code,name from b2"

b1,b2两表结构相同，第4句成功，但第5句出错，WHY？

提示[Microsoft][Odbc visual Foxpro Driver]  syntax error

[此贴子已经被作者于2003-9-28 14:22:22编辑过]

cnwang

诸位关照下呀，我每月都有数百上千个DBF文件要用此法合并，用逐行逐字段的方法效率实在太低。

zhuyiwen

Sub CopyTest()
    Dim dbf As ADODB.Connection
   
    Set dbf = CurrentProject.Connection
    dbf.Execute "INSERT INTO b1 (code, name) " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;] " & _
                "SELECT code, name FROM b2 " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;]"
End Sub

' Good Lucky!

[em24]

zhuyiwen

因为[Microsoft][Odbc visual Foxpro Driver]不支持"INSERT INTO ... SELECT ..."语句, 因此需要改成JET完成:

Sub CopyTest()
    Dim dbf As ADODB.Connection
   
    Set dbf = CurrentProject.Connection
    dbf.Execute "INSERT INTO b1 (code, name) " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;] " & _
                "SELECT code, name FROM b2 " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;]"
End Sub

cnwang

用于FOXPRO2.5格式时依然出错，ACCESS2K环境，是否要加装什么驱动？

zhuyiwen

把你的DBF文件贴两个上来，看看

cnwang

劳驾啦：

zhuyiwen

Sub CopyTest()
    Dim dbf As ADODB.Connection
   
    Call SetDBFTag("E:\IN_PORT\DW1.dbf")
    Call SetDBFTag("E:\IN_PORT\DW2.dbf")
    Set dbf = CurrentProject.Connection
    dbf.Execute "INSERT INTO dw1 (code, name) " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;] " & _
                "SELECT code, name FROM dw2 " & _
                    "IN '' [dBASE IV; Database=E:\IN_PORT;]"
End Sub

Sub SetDBFTag(ByVal strFileName As String)
    Dim FileNumber As Integer
    Dim byteTag As Byte
   
    FileNumber = FreeFile
    Open strFileName For Random As #FileNumber Len = 1
    Get #FileNumber, 1, byteTag
    Debug.Print byteTag
    Let byteTag = 3
    Put #FileNumber, 1, byteTag
    Close #FileNumber
End Sub

cnwang

测试通过，爽呆啦。

zhuyiwen

即将DBF文件的第一个字节改为03,即转换成DBASEIII的数据库文件格式.

[em24]