Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: OO4O Help (Automation Error)

    I need some help. I have this VBA Code that I used previously to insert rows into an Oracle database. I have since modified it to meet my needs and I'm getting this:

    Code:
    Run-Time Error '440' (Automation Error)
    when I try to run the macro. The error doesn't tell me where it's happening and why. Here is the macro code:

    Code:
    Sub Insert_Data()
        
        Dim OraSession As Object
        Dim OraDatabase As Object
        
        Dim ACellVar As String
        Dim R As Long
        
        Set ACellRange = Range("A2")
        R = 1
        Do
        If IsEmpty(ACellRange.Cells(R, 1)) Then Exit Do
        'ACellVar = ACellRange.Cells(R, 1).Text
        PSIMKEY = Val(ACellRange.Cells(R, 1).Text)
        PSCPKEY = Val(ACellRange.Cells(R, 2).Text)
        PSPIECENO = Val(ACellRange.Cells(R, 3).Text)
        PSCODE = Val(ACellRange.Cells(R, 4).Text)
        PSCRDATE = Format$(Now, "dd-mmm-yyyy")
        PSMDDATE = Format$(Now, "dd-mmm-yyyy")
        PSOPNUM = Val(ACellRange.Cells(R, 7).Text)
        PSDIM1 = Val(ACellRange.Cells(R, 8).Text)
        PSDIM2 = Val(ACellRange.Cells(R, 9).Text)
        PSDIM3 = Val(ACellRange.Cells(R, 10).Text)
        PSQTYP = Val(ACellRange.Cells(R, 11).Text)
        PSQTYL = Val(ACellRange.Cells(R, 12).Text)
        PSSRATE = Val(ACellRange.Cells(R, 13).Text)
        PSOFFSET = Val(ACellRange.Cells(R, 14).Text)
        PSESTCOST = Val(ACellRange.Cells(R, 15).Text)
        PSEDATE = Format$(Now, "dd-mmm-yyyy")
        PSDDATE = Format$(Now, "dd-mmm-yyyy")
        PSREV = Val(ACellRange.Cells(R, 18).Text)
        PSTYPE = Val(ACellRange.Cells(R, 19).Text)
        PSECODE = Val(ACellRange.Cells(R, 20).Text)
        PSDESCR = Val(ACellRange.Cells(R, 21).Text)
        PSCERT1 = Val(ACellRange.Cells(R, 22).Text)
        PSCERT2 = Val(ACellRange.Cells(R, 23).Text)
        PSCERT3 = Val(ACellRange.Cells(R, 24).Text)
        PSCERT4 = Val(ACellRange.Cells(R, 25).Text)
        R = R + 1
        
        'Insert Rows
        Set OraSession = CreateObject("OracleInProcServer.XOraSession")
        Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)
        OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO,PS_CODE" & _
        "PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS_DIM_3,PS_QTY_P,PS_QTY_L" & _
        "PS_S_RATE,PS_OFFSET,PS_EST_COST,PS_E_DATE,PS_D_DATE,PS_REV,PS_TYPE,PS_E_CODE" & _
        "PS_DESCR,PS_CERT1,PS_CERT2,PS_CERT3,PS_CERT4) VALUES ('" & PSIMKEY & "','" & _
        PSCPKEY & "','" & PSPIECENO & "','" & PSCODE & "','" & PSCRDATE & "','" & _
        PSMDDATE & "','" & PSOPNUM & "','" & PSDIM1 & "','" & PSDIM2 & "','" & PSDIM3 & _
        "','" & PSQTYP & "','" & PSQTYL & "','" & PSSRATE & "','" & PSOFFSET & "','" & _
        PSESTCOST & "','" & PSEDATE & "','" & PSDDATE & "','" & PSREV & "','" & PSTYPE & _
        "','" & PSECODE & "','" & PSCERT1 & "','" & PSCERT2 & "','" & PSCERT3 & "','" & _
        PSCERT4 & "')")
        Loop
    End Sub
    Can someone see why this would happen? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Just a thought: maybe you've forgotten a comma after PS_CODE?

    OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO,PS_CODE" & _
    " PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Thanks for noticing but that isn't it. If it were, I'd get an invalid column name error. I tried it anyways but to no avail. I'm not even getting to the insert statement I don't think. Thanks though, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Originally posted by JCScoobyRS
    Thanks for noticing but that isn't it. If it were, I'd get an invalid column name error. I tried it anyways but to no avail. I'm not even getting to the insert statement I don't think. Thanks though, Jeremy
    Another thought: you have incorporated these two statements in the loop, thereby executing them more than once.

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)

    I suppose executing them once is enough, try what happens if you put them before the Do statement.

    Also, I would add these two statements just before the End Sub, to be sure to clean up resources:

    Set OraDatabase = Nothing
    Set OraSession = Nothing

    Hope this helps.

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Thanks again but no dice. I don't understand. I would almost think that the problem would be with the data being inserted but I don't even think that it gets that far. I'm lost. Later, J
    Nothing better than a good ride.

  6. #6
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Here is the worksheet that I'm working with. I thought this would be helpful and if you have time, give it a look. Hopefully, you will see something that I'm not. Thanks, Jeremy
    Attached Files Attached Files
    Nothing better than a good ride.

  7. #7
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Originally posted by JCScoobyRS
    Here is the worksheet that I'm working with. I thought this would be helpful and if you have time, give it a look. Hopefully, you will see something that I'm not. Thanks, Jeremy
    I've looked very briefly into it, but I can't see anything strange there. I'm starting to think that the Oracle client installation on your PC is somehow broken. You might try to reinstall it.

    Best of luck.

  8. #8
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I tested the connection and the macro is in fact connecting to the database. I am beginning to wonder if it's my insert statement or possibly user permissions. Can you scan the insert statement to see if you can find any problems? Thanks, Jeremy
    Nothing better than a good ride.

  9. #9
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    It's fixed. I don't know how but I retyped my insert statement and there must had been an error somewhere in there. Here is my new/working code:

    Code:
    Sub Insert_Data()
        
        Dim OraSession As Object
        Dim OraDatabase As Object
        
        Dim ACellVar As String
        Dim R As Long
        
    '   *** Change starting address to suit ***
        
        Set ACellRange = Range("A2")
        R = 1
        
        'Create Connection
        Set OraSession = CreateObject("OracleInProcServer.XOraSession")
        Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)
        
        Do
        If IsEmpty(ACellRange.Cells(R, 1)) Then Exit Do
        'ACellVar = ACellRange.Cells(R, 1).Text
        PSIMKEY = ACellRange.Cells(R, 1).Text
        PSCPKEY = ACellRange.Cells(R, 2).Text
        PSPIECENO = ACellRange.Cells(R, 3).Text
        PSCODE = ACellRange.Cells(R, 4).Text
        PSCRDATE = Format$(Now, "dd-mmm-yyyy")
        PSMDDATE = Format$(Now, "dd-mmm-yyyy")
        PSOPNUM = Val(ACellRange.Cells(R, 7).Text)
        PSDIM1 = Val(ACellRange.Cells(R, 8).Text)
        PSDIM2 = Val(ACellRange.Cells(R, 9).Text)
        PSDIM3 = Val(ACellRange.Cells(R, 10).Text)
        PSQTYP = Val(ACellRange.Cells(R, 11).Text)
        PSQTYL = Val(ACellRange.Cells(R, 12).Text)
        PSSRATE = Val(ACellRange.Cells(R, 13).Text)
        PSOFFSET = Val(ACellRange.Cells(R, 14).Text)
        PSESTCOST = Val(ACellRange.Cells(R, 15).Text)
        PSEDATE = Format$(Now, "dd-mmm-yyyy")
        PSDDATE = Format$(Now, "dd-mmm-yyyy")
        PSREV = ACellRange.Cells(R, 18).Text
        PSTYPE = ACellRange.Cells(R, 19).Text
        PSECODE = ACellRange.Cells(R, 20).Text
        PSDESCR = ACellRange.Cells(R, 21).Text
        PSCERT1 = ACellRange.Cells(R, 22).Text
        PSCERT2 = ACellRange.Cells(R, 23).Text
        PSCERT3 = ACellRange.Cells(R, 24).Text
        PSCERT4 = ACellRange.Cells(R, 25).Text
        R = R + 1
        
        'Insert Rows
        OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO," & _
        "PS_CODE,PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS_DIM_3," & _
        "PS_QTY_P,PS_QTY_L,PS_S_RATE,PS_OFFSET,PS_EST_COST,PS_E_DATE,PS_D_DATE," & _
        "PS_REV,PS_TYPE,PS_E_CODE,PS_DESCR,PS_CERT1,PS_CERT2,PS_CERT3,PS_CERT4)" & _
        "VALUES ('" & PSIMKEY & "','" & PSCPKEY & "','" & PSPIECENO & "','" & _
        PSCODE & "','" & PSCRDATE & "','" & PSMDDATE & "','" & PSOPNUM & "','" & _
        PSDIM1 & "','" & PSDIM2 & "','" & PSDIM3 & "','" & PSQTYP & "','" & _
        PSQTYL & "','" & PSSRATE & "','" & PSOFFSET & "','" & PSESTCOST & "','" & _
        PSEDATE & "','" & PSDDATE & "','" & PSREV & "','" & PSTYPE & "','" & _
        PSECODE & "','" & PSDESCR & "','" & PSCERT1 & "','" & PSCERT2 & "','" & _
        PSCERT3 & "','" & PSCERT4 & "')")
        Loop
        
        Set OraDatabase = Nothing
        Set OraSession = Nothing
    
    End Sub
    Thanks alot, Jeremy
    Nothing better than a good ride.

  10. #10
    Join Date
    Jul 2004
    Posts
    3

    Unhappy Help!!!

    Quote Originally Posted by JCScoobyRS
    It's fixed. I don't know how but I retyped my insert statement and there must had been an error somewhere in there. Here is my new/working code:

    Code:
    Sub Insert_Data()
        
        Dim OraSession As Object
        Dim OraDatabase As Object
        
        Dim ACellVar As String
        Dim R As Long
        
    '   *** Change starting address to suit ***
        
        Set ACellRange = Range("A2")
        R = 1
        
        'Create Connection
        Set OraSession = CreateObject("OracleInProcServer.XOraSession")
        Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)
        
        Do
        If IsEmpty(ACellRange.Cells(R, 1)) Then Exit Do
        'ACellVar = ACellRange.Cells(R, 1).Text
        PSIMKEY = ACellRange.Cells(R, 1).Text
        PSCPKEY = ACellRange.Cells(R, 2).Text
        PSPIECENO = ACellRange.Cells(R, 3).Text
        PSCODE = ACellRange.Cells(R, 4).Text
        PSCRDATE = Format$(Now, "dd-mmm-yyyy")
        PSMDDATE = Format$(Now, "dd-mmm-yyyy")
        PSOPNUM = Val(ACellRange.Cells(R, 7).Text)
        PSDIM1 = Val(ACellRange.Cells(R, 8).Text)
        PSDIM2 = Val(ACellRange.Cells(R, 9).Text)
        PSDIM3 = Val(ACellRange.Cells(R, 10).Text)
        PSQTYP = Val(ACellRange.Cells(R, 11).Text)
        PSQTYL = Val(ACellRange.Cells(R, 12).Text)
        PSSRATE = Val(ACellRange.Cells(R, 13).Text)
        PSOFFSET = Val(ACellRange.Cells(R, 14).Text)
        PSESTCOST = Val(ACellRange.Cells(R, 15).Text)
        PSEDATE = Format$(Now, "dd-mmm-yyyy")
        PSDDATE = Format$(Now, "dd-mmm-yyyy")
        PSREV = ACellRange.Cells(R, 18).Text
        PSTYPE = ACellRange.Cells(R, 19).Text
        PSECODE = ACellRange.Cells(R, 20).Text
        PSDESCR = ACellRange.Cells(R, 21).Text
        PSCERT1 = ACellRange.Cells(R, 22).Text
        PSCERT2 = ACellRange.Cells(R, 23).Text
        PSCERT3 = ACellRange.Cells(R, 24).Text
        PSCERT4 = ACellRange.Cells(R, 25).Text
        R = R + 1
        
        'Insert Rows
        OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO," & _
        "PS_CODE,PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS_DIM_3," & _
        "PS_QTY_P,PS_QTY_L,PS_S_RATE,PS_OFFSET,PS_EST_COST,PS_E_DATE,PS_D_DATE," & _
        "PS_REV,PS_TYPE,PS_E_CODE,PS_DESCR,PS_CERT1,PS_CERT2,PS_CERT3,PS_CERT4)" & _
        "VALUES ('" & PSIMKEY & "','" & PSCPKEY & "','" & PSPIECENO & "','" & _
        PSCODE & "','" & PSCRDATE & "','" & PSMDDATE & "','" & PSOPNUM & "','" & _
        PSDIM1 & "','" & PSDIM2 & "','" & PSDIM3 & "','" & PSQTYP & "','" & _
        PSQTYL & "','" & PSSRATE & "','" & PSOFFSET & "','" & PSESTCOST & "','" & _
        PSEDATE & "','" & PSDDATE & "','" & PSREV & "','" & PSTYPE & "','" & _
        PSECODE & "','" & PSDESCR & "','" & PSCERT1 & "','" & PSCERT2 & "','" & _
        PSCERT3 & "','" & PSCERT4 & "')")
        Loop
        
        Set OraDatabase = Nothing
        Set OraSession = Nothing
    
    End Sub
    Thanks alot, Jeremy
    Hey guys, am trying to do almost the same thing (except I'm updating). Problem is I get Run-time error '429', Activex Component can't create object. And the line that's causing it is this, Set OraSession = CreateObject("OracleInProcServer.XOraSession").

    Do I need to install any reference? Or am I missing anything?

    Thanks in advance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •