Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 2737|回复: 2

vba导入 oracle,提示ora_00913错误,如何解决?

[复制链接]
发表于 2013-2-25 15:39 | 显示全部楼层 |阅读模式
现在想将一批数据导入到oracle时,可能由于字段过多,提示ora-00913错误,请教如何处理?
主要代码:
Dim r, c As Single
       Dim sqlStr As String
       For r = 1 To 100
           For c = 1 To 11
           Set rs = New ADODB.Recordset
           Dim CellsValue(11) As String
           CellsValue(c - 1) = ThisWorkbook.ActiveSheet.Cells(r, c).Value
           Next c
         
          sqlStr = "Insert Into fenghuang3(Id_num,code,name,guba,income,price,up_down_per,up_down,chengjiao,date_id,history) VALUES('" & CellsValue(0) & "','" & CellsValue(1) & "','" & CellsValue(2) & "','" & CellsValue(3) & "','" & CellsValue(4) & "','" & CellsValue(5) & "','" & CellsValue(6) & "','" & CellsValue(7) & "','" & CellsValue(8) & "','" & CellsValue(9) & "','" & CellsValue(10) & "','" & CellsValue(11) & "')"
           

          rs.Open sqlStr, conn

提示错误:


发表于 2013-2-25 16:00 | 显示全部楼层
回复

使用道具 举报

 楼主| 发表于 2013-2-26 08:35 | 显示全部楼层
前面有链接定义:
  Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strHost, strDatabase, pwd, strUser, strPassword As String
    Set conn = New ADODB.Connection
     
     
    strHost = "localhost"
    strDatabase = "ORCL"
     
    strUser = "system"
    strPassword = "test"
     
    conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(DESCRIPTION=" & _
    "(ADDRESS=(PROTOCOL=TCP)" & _
    "(HOST=" & strHost & ")(PORT=1521))" & _
    "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
     
    conn.CommandTimeout = 100000
     
    conn.Open
     
    Set rs = New ADODB.Recordset
     
    rs.Open "fenghuang3", conn, adOpenKeyset, adLockBatchOptimistic

因为代码也是东拼西凑的,现在怀疑String类型长度、CellsValue、以及vba跟oracle对接限制有问题。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-6-17 01:04 , Processed in 0.243272 second(s), 12 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表