Dim cn As New ADODB.Connection
Dim totalrs As Long
Dim Comtxt As String
Comtxt = "select *FROM[Sheet1$] "
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\customers.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
.Open()
End With
rs.Open(Comtxt, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)
totalrs = rs.RecordCount
myConn.Execute("delete * from TB_customer")
Dim i As Long
'Try
For i = 0 To totalrs - 1
myConn.Execute("insert into TB_customer([Cust_id],[Cust_name],[Cust_contact]) values ('" & rs(0).Value.ToString & "','" & rs(1).Value.ToString & "','" & rs(2).Value.ToString & "')")
rs.MoveNext()
Next
rs.Close()
rs = Nothing
cn.Close()
Dim cn1 As New ADODB.Connection
Dim totalrs1 As Long
Dim Comtxt1 As String
Comtxt1 = "select *FROM[Sheet1$] "
With cn1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Order.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
.Open()
End With
rs2.Open(Comtxt1, cn1, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)
totalrs1 = rs2.RecordCount
myConn.Execute("delete * from TB_Order")
Dim j As Long
'Try
For j = 0 To totalrs1 - 1
myConn.Execute("insert into TB_Order([Cust_id],[Product],[Price],[Order_date]) values ('" & rs2(0).Value.ToString & "','" & rs2(1).Value.ToString & "','" & rs2(2).Value.ToString & "','" & rs2(3).Value.ToString & "')") (这句话出错,错处出错信息如下:)
rs2.MoveNext()
Next
rs2.Close()
rs2 = Nothing
cn1.Close()
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
5 个解决方案
#1
Comtxt1 = "select * FROM [Sheet1$]"
#2
Comtxt1和Comtxt 的值不能相等吗?
#3
1.查看TB_Order和TB_customer是否存在主丛表关系
2.查看第二个表中的主键列数据是否有相同的
2.查看第二个表中的主键列数据是否有相同的
#4
TB_customer 里面和TB_Order的主键是同一个,都是id号
请问这要怎么做啊?
请问这要怎么做啊?
#5
我已经将TB_Order 的Indexed这一项改成Yes (Duplicates OK)
同时将primary key这一项在id那一栏去掉就好了
谢谢楼上的各位了
#1
Comtxt1 = "select * FROM [Sheet1$]"
#2
Comtxt1和Comtxt 的值不能相等吗?
#3
1.查看TB_Order和TB_customer是否存在主丛表关系
2.查看第二个表中的主键列数据是否有相同的
2.查看第二个表中的主键列数据是否有相同的
#4
TB_customer 里面和TB_Order的主键是同一个,都是id号
请问这要怎么做啊?
请问这要怎么做啊?
#5
我已经将TB_Order 的Indexed这一项改成Yes (Duplicates OK)
同时将primary key这一项在id那一栏去掉就好了
谢谢楼上的各位了