Office中国论坛/Access中国论坛

标题: ado 中 insert 方法出错 [打印本页]

作者: cnwang 时间: 2003-9-28 22:19
标题: ado 中 insert 方法出错

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 时间: 2003-9-29 17:45
诸位关照下呀，我每月都有数百上千个DBF文件要用此法合并，用逐行逐字段的方法效率实在太低。

作者: zhuyiwen 时间: 2003-9-29 19:00
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 时间: 2003-9-29 19:03
因为[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 时间: 2003-9-29 22:41
用于FOXPRO2.5格式时依然出错，ACCESS2K环境，是否要加装什么驱动？

作者: zhuyiwen 时间: 2003-9-29 23:01
把你的DBF文件贴两个上来，看看

作者: cnwang 时间: 2003-10-1 01:25

劳驾啦：
[attach]1873[/attach]

作者: zhuyiwen 时间: 2003-10-9 19:34
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 时间: 2003-10-9 19:51
测试通过，爽呆啦。

作者: zhuyiwen 时间: 2003-10-16 19:49
即将DBF文件的第一个字节改为03,即转换成DBASEIII的数据库文件格式.

[em24]

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)